Paragraph Format from Word in Memo Field

[Eric]

I export several form fields from from MS Word to Access. These fields are
memo fields. The text shows in Access with the Paragraph Carrage Return
formating symbles as little squares. Is there a way to remove these (End of
Paragraph) marks using VB code or some other setting?

Thanks in advance!

 

[Ted]

I would use the InStr function to find each Carriage Return and remove them.
InStr(1, strMemo, Chr(13)) will give you the position of the first Carriage
Return.
Then use the Left and Right string functions to "cut out" the Carriage
Returns.
Example: strMemo = Left(strMemo, InStr(1, strMemo, Chr(13) -1) &
Right(strMemo, Len(strMemo) - InStr(1, strMemo, Chr(13)))

Good Luck!

 

[Eric]

Thanks Ted, I'll try it...... But where do I put the InStr function to make
this automatic or a macro type command?

I am familiar with VB Code in Access, but never used anything like this in
Access.

 

[John Spencer]

You can use the Replace function in an update query. You need to identify the
character that is causing the problem. It could be a carriage return
[Chr(13)]or a line feed [Chr(10)] or some other character.

Assumptions:
You want to remove the carriage return and replace it with a space.
The character to be replaced is a carriage return - Chr(13)

SQL Statement would look like:
UPDATE YourTable
SET YourField = Replace(YourField,Chr(13)," ")
WHERE YourField Like "*" & Chr(13) & "*"

If you want you can Replace to show a new line
UPDATE YourTable
SET YourField = Replace(YourField,Chr(13),Chr(13) & Chr(10))
WHERE YourField Like "*" & Chr(13) & "*"

If you don't know how to build an update query without using the query grid,
post back.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Eric]

Ok John I'll give that a try. I think I can figure it out...... Thanks so
much you guys/gals are awesome!!
--
Eric the Rookie

You can use the Replace function in an update query. You need to identify the
character that is causing the problem. It could be a carriage return
[Chr(13)]or a line feed [Chr(10)] or some other character.

Assumptions:
You want to remove the carriage return and replace it with a space.
The character to be replaced is a carriage return - Chr(13)

SQL Statement would look like:
UPDATE YourTable
SET YourField = Replace(YourField,Chr(13)," ")
WHERE YourField Like "*" & Chr(13) & "*"

If you want you can Replace to show a new line
UPDATE YourTable
SET YourField = Replace(YourField,Chr(13),Chr(13) & Chr(10))
WHERE YourField Like "*" & Chr(13) & "*"

If you don't know how to build an update query without using the query grid,
post back.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I export several form fields from from MS Word to Access. These fields are
memo fields. The text shows in Access with the Paragraph Carrage Return
formating symbles as little squares. Is there a way to remove these (End of
Paragraph) marks using VB code or some other setting?

Thanks in advance!

 

[Eric]

John, tried to use the sql statement without the query grid by pasting this
code in the SQL View:

UPDATE [All ReviewEXS]
SET ExecutiveSummary = Replace(ExecutiveSummary,Chr(13),Chr(13) & Chr(10))
WHERE ExecutiveSummary "
" & Chr(13) & ""

First time using this method...... The symbol shown in the imported memo
field is a little square that can't be shown here. It won't even paste in
the sql statement. Asside from that..... Am I doing this right?

Thanks
--
Eric the Rookie

You can use the Replace function in an update query. You need to identify the
character that is causing the problem. It could be a carriage return
[Chr(13)]or a line feed [Chr(10)] or some other character.

Assumptions:
You want to remove the carriage return and replace it with a space.
The character to be replaced is a carriage return - Chr(13)

SQL Statement would look like:
UPDATE YourTable
SET YourField = Replace(YourField,Chr(13)," ")
WHERE YourField Like "*" & Chr(13) & "*"

If you want you can Replace to show a new line
UPDATE YourTable
SET YourField = Replace(YourField,Chr(13),Chr(13) & Chr(10))
WHERE YourField Like "*" & Chr(13) & "*"

If you don't know how to build an update query without using the query grid,
post back.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I export several form fields from from MS Word to Access. These fields are
memo fields. The text shows in Access with the Paragraph Carrage Return
formating symbles as little squares. Is there a way to remove these (End of
Paragraph) marks using VB code or some other setting?

Thanks in advance!

 

[John Spencer]

CLOSE

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [All ReviewEXS]
SET ExecutiveSummary = Replace(ExecutiveSummary,Chr(13),Chr(13) & Chr(10))
WHERE ExecutiveSummary LIKE "*" & Chr(13) & "*"

If you know the position of one of the squares, you might be able to use
Asc(Mid(theField,22)) to get the Ascii value of the square.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Eric]

John, your right I think I am getting close. I have realized that the little
square is a standard Carrige Return/End of Paragraph mark, that is ussually
hidden. The only way I found this out was to copy the text to notepad and
selected "Reveal Formating". All this was fine except because the charactor
normally hidden I can't cut it and paste the darn thing anywhere. It just
shows up as a blank.

I will try the corrected code by pasting this "blank" thing into the " ".
But I'm not confident.

"

a"

Shown above between the " " is actually a cut and paste of the "end of
Paragragh" marks. two before the a.

Ok I know it does'nt get weirder than this post...

"If it is - what it is"? Then what is it?


--
Eric the Rookie

CLOSE

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [All ReviewEXS]
SET ExecutiveSummary = Replace(ExecutiveSummary,Chr(13),Chr(13) & Chr(10))
WHERE ExecutiveSummary LIKE "*" & Chr(13) & "*"

If you know the position of one of the squares, you might be able to use
Asc(Mid(theField,22)) to get the Ascii value of the square.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John, tried to use the sql statement without the query grid by pasting this
code in the SQL View:

UPDATE [All ReviewEXS]
SET ExecutiveSummary = Replace(ExecutiveSummary,Chr(13),Chr(13) & Chr(10))
WHERE ExecutiveSummary "
" & Chr(13) & ""

First time using this method...... The symbol shown in the imported memo
field is a little square that can't be shown here. It won't even paste in
the sql statement. Asside from that..... Am I doing this right?

Thanks

 

[Apprentice]

Ok, I think I got it. Now I want to update several fields from the same
table with one update query, but I'm getting a syntex error. I think my
comma's are in the wrong place. Here is what I have.

UPDATE ReviewBPS SET Background = Replace(Background, Chr(13), Chr(13) &
Chr(10))
WHERE Background Like "*" & Chr(13) & "*",

UPDATE ReviewBPS SET Purpose = Replace(Purpose, Chr(13), Chr(13) & Chr(10))
WHERE Purpose Like "*" & Chr(13) & "*",

UPDATE ReviewBPS SET Scope = Replace(Scope, Chr(13), Chr(13) & Chr(10))
WHERE Scope Like "*" & Chr(13) & "*";
--
Your guidance is greatly appreciated!

John, your right I think I am getting close. I have realized that the little
square is a standard Carrige Return/End of Paragraph mark, that is ussually
hidden. The only way I found this out was to copy the text to notepad and
selected "Reveal Formating". All this was fine except because the charactor
normally hidden I can't cut it and paste the darn thing anywhere. It just
shows up as a blank.

I will try the corrected code by pasting this "blank" thing into the " ".
But I'm not confident.

"

a"

Shown above between the " " is actually a cut and paste of the "end of
Paragragh" marks. two before the a.

Ok I know it does'nt get weirder than this post...

"If it is - what it is"? Then what is it?


--
Eric the Rookie

CLOSE

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [All ReviewEXS]
SET ExecutiveSummary = Replace(ExecutiveSummary,Chr(13),Chr(13) & Chr(10))
WHERE ExecutiveSummary LIKE "*" & Chr(13) & "*"

If you know the position of one of the squares, you might be able to use
Asc(Mid(theField,22)) to get the Ascii value of the square.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John, tried to use the sql statement without the query grid by pasting this
code in the SQL View:

UPDATE [All ReviewEXS]
SET ExecutiveSummary = Replace(ExecutiveSummary,Chr(13),Chr(13) & Chr(10))
WHERE ExecutiveSummary "
" & Chr(13) & ""

First time using this method...... The symbol shown in the imported memo
field is a little square that can't be shown here. It won't even paste in
the sql statement. Asside from that..... Am I doing this right?

Thanks

 

[John Spencer]

In Access you would have to run that three as three separate queries.

To do this all in one query, you would need to run the query as:

UPDATE ReviewBPS
SET
Background = Replace([Background], Chr(13), Chr(13) & Chr(10))
, Purpose = Replace([Purpose], Chr(13), Chr(13) & Chr(10))
, Scope = Replace([Scope], Chr(13), Chr(13) & Chr(10))
WHERE Background Like "*" & Chr(13) & "*"
OR Purpose Like "*" & Chr(13) & "*"
OR Scope Like "*" & Chr(13) & "*";

The above would update the three fields in one query. It would however have
create errors if one or two fields in the record was null.

So you might need to use a more complex expression where you check if each
field is null or not. Something like the following for each of the fields.

Background = IIF([Background] Is not Null, Replace([Background], Chr(13),
Chr(13) & Chr(10)),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Apprentice]

John thanks, this looks like what I need. I think there may need to be a
"Then" somewhere in the code. Here is what I have.... it errors out with an
unidentified "IFF".

UPDATE ReviewBPS SET Background = IFF ([Background] Is not Null, Replace
([Background], Chr(13), Chr(13) & Chr(10)), Null), Purpose = IFF ([Purpose]
Is not Null, Replace([Purpose], Chr(13), Chr(13) & Chr(10)), Null), Scope =
IFF ([Scope] Is not Null, Replace([Scope], Chr(13), Chr(13) & Chr(10)), Null)
WHERE Background Like "*" & Chr(13) & "*"
OR Purpose Like "*" & Chr(13) & "*"
OR Scope Like "*" & Chr(13) & "*";
--
Your guidance is greatly appreciated!

In Access you would have to run that three as three separate queries.

To do this all in one query, you would need to run the query as:

UPDATE ReviewBPS
SET
Background = Replace([Background], Chr(13), Chr(13) & Chr(10))
, Purpose = Replace([Purpose], Chr(13), Chr(13) & Chr(10))
, Scope = Replace([Scope], Chr(13), Chr(13) & Chr(10))
WHERE Background Like "*" & Chr(13) & "*"
OR Purpose Like "*" & Chr(13) & "*"
OR Scope Like "*" & Chr(13) & "*";

The above would update the three fields in one query. It would however have
create errors if one or two fields in the record was null.

So you might need to use a more complex expression where you check if each
field is null or not. Something like the following for each of the fields.

Background = IIF([Background] Is not Null, Replace([Background], Chr(13),
Chr(13) & Chr(10)),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Ok, I think I got it. Now I want to update several fields from the same
table with one update query, but I'm getting a syntex error. I think my
comma's are in the wrong place. Here is what I have.

UPDATE ReviewBPS SET Background = Replace(Background, Chr(13), Chr(13) &
Chr(10))
WHERE Background Like "*" & Chr(13) & "*",

UPDATE ReviewBPS SET Purpose = Replace(Purpose, Chr(13), Chr(13) & Chr(10))
WHERE Purpose Like "*" & Chr(13) & "*",

UPDATE ReviewBPS SET Scope = Replace(Scope, Chr(13), Chr(13) & Chr(10))
WHERE Scope Like "*" & Chr(13) & "*";

 

[Douglas J. Steele]

It's IIf, not Iff.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John thanks, this looks like what I need. I think there may need to be a
"Then" somewhere in the code. Here is what I have.... it errors out with
an
unidentified "IFF".

UPDATE ReviewBPS SET Background = IFF ([Background] Is not Null, Replace
([Background], Chr(13), Chr(13) & Chr(10)), Null), Purpose = IFF
([Purpose]
Is not Null, Replace([Purpose], Chr(13), Chr(13) & Chr(10)), Null), Scope
=
IFF ([Scope] Is not Null, Replace([Scope], Chr(13), Chr(13) & Chr(10)),
Null)
WHERE Background Like "*" & Chr(13) & "*"
OR Purpose Like "*" & Chr(13) & "*"
OR Scope Like "*" & Chr(13) & "*";
--
Your guidance is greatly appreciated!

In Access you would have to run that three as three separate queries.

To do this all in one query, you would need to run the query as:

UPDATE ReviewBPS
SET
Background = Replace([Background], Chr(13), Chr(13) & Chr(10))
, Purpose = Replace([Purpose], Chr(13), Chr(13) & Chr(10))
, Scope = Replace([Scope], Chr(13), Chr(13) & Chr(10))
WHERE Background Like "*" & Chr(13) & "*"
OR Purpose Like "*" & Chr(13) & "*"
OR Scope Like "*" & Chr(13) & "*";

The above would update the three fields in one query. It would however
have
create errors if one or two fields in the record was null.

So you might need to use a more complex expression where you check if
each
field is null or not. Something like the following for each of the
fields.

Background = IIF([Background] Is not Null, Replace([Background], Chr(13),
Chr(13) & Chr(10)),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Ok, I think I got it. Now I want to update several fields from the
same
table with one update query, but I'm getting a syntex error. I think
my
comma's are in the wrong place. Here is what I have.

UPDATE ReviewBPS SET Background = Replace(Background, Chr(13), Chr(13)
&
Chr(10))
WHERE Background Like "*" & Chr(13) & "*",

UPDATE ReviewBPS SET Purpose = Replace(Purpose, Chr(13), Chr(13) &
Chr(10))
WHERE Purpose Like "*" & Chr(13) & "*",

UPDATE ReviewBPS SET Scope = Replace(Scope, Chr(13), Chr(13) & Chr(10))
WHERE Scope Like "*" & Chr(13) & "*";

 

[Apprentice]

I have to laugh at myself here. Sorry, but thanks for the attention to
detail.... I'll give that a try.
--
Your guidance is greatly appreciated!

It's IIf, not Iff.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John thanks, this looks like what I need. I think there may need to be a
"Then" somewhere in the code. Here is what I have.... it errors out with
an
unidentified "IFF".

UPDATE ReviewBPS SET Background = IFF ([Background] Is not Null, Replace
([Background], Chr(13), Chr(13) & Chr(10)), Null), Purpose = IFF
([Purpose]
Is not Null, Replace([Purpose], Chr(13), Chr(13) & Chr(10)), Null), Scope
=
IFF ([Scope] Is not Null, Replace([Scope], Chr(13), Chr(13) & Chr(10)),
Null)
WHERE Background Like "*" & Chr(13) & "*"
OR Purpose Like "*" & Chr(13) & "*"
OR Scope Like "*" & Chr(13) & "*";
--
Your guidance is greatly appreciated!

In Access you would have to run that three as three separate queries.

To do this all in one query, you would need to run the query as:

UPDATE ReviewBPS
SET
Background = Replace([Background], Chr(13), Chr(13) & Chr(10))
, Purpose = Replace([Purpose], Chr(13), Chr(13) & Chr(10))
, Scope = Replace([Scope], Chr(13), Chr(13) & Chr(10))
WHERE Background Like "*" & Chr(13) & "*"
OR Purpose Like "*" & Chr(13) & "*"
OR Scope Like "*" & Chr(13) & "*";

The above would update the three fields in one query. It would however
have
create errors if one or two fields in the record was null.

So you might need to use a more complex expression where you check if
each
field is null or not. Something like the following for each of the
fields.

Background = IIF([Background] Is not Null, Replace([Background], Chr(13),
Chr(13) & Chr(10)),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Apprentice wrote:
Ok, I think I got it. Now I want to update several fields from the
same
table with one update query, but I'm getting a syntex error. I think
my
comma's are in the wrong place. Here is what I have.

UPDATE ReviewBPS SET Background = Replace(Background, Chr(13), Chr(13)
&
Chr(10))
WHERE Background Like "*" & Chr(13) & "*",

UPDATE ReviewBPS SET Purpose = Replace(Purpose, Chr(13), Chr(13) &
Chr(10))
WHERE Purpose Like "*" & Chr(13) & "*",

UPDATE ReviewBPS SET Scope = Replace(Scope, Chr(13), Chr(13) & Chr(10))
WHERE Scope Like "*" & Chr(13) & "*";