Need to Identify Records with Carriage Returns within a Memo Field

[MJ]

My challenge is two-fold, I need to ...

1) Identify the number of records within a database table where two memo
fields contain entries with carriage returns in the memos; and

2) Replace the carriage returns with a space.

Thank you in adavance for your assistance.

MJ

 

[Douglas J. Steele]

UPDATE MyTable
SET MyMemoField = Replace(MyMemoField, Chr(13) & Chr(10), " ")
WHERE InStr(MyMemoField, Chr(13) & Chr(10)) > 0

 

[MJ]

Douglas,

Sorry it has taken this long to get back to this issue. Your script has
been helpful, but I have some other questions along this line.

Does the order of the ascii characters make a difference:
Chr(13) & Chr(10);
Chr(10) & Chr(13); OR does it make any difference?

Furthermore should I be checking for the "line feed/new line" [Chr(10)] and
"carriage return" [Chr(13)] separately as well?

Is it possible for them to show up by themselves within memos fields rather
than paired?

Thanks for your assistance.
MJ

 

[Douglas J. Steele]

Yes, the order makes a difference. It must be Chr(13) & Chr(10).

It's possible for there to be only one of the two characters, but in Access
it'll show up as a square block rather than a new line.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Sorry it has taken this long to get back to this issue. Your script has
been helpful, but I have some other questions along this line.

Does the order of the ascii characters make a difference:
Chr(13) & Chr(10);
Chr(10) & Chr(13); OR does it make any difference?

Furthermore should I be checking for the "line feed/new line" [Chr(10)]
and
"carriage return" [Chr(13)] separately as well?

Is it possible for them to show up by themselves within memos fields
rather
than paired?

Thanks for your assistance.
MJ

UPDATE MyTable
SET MyMemoField = Replace(MyMemoField, Chr(13) & Chr(10), " ")
WHERE InStr(MyMemoField, Chr(13) & Chr(10)) > 0

 

[MJ]

Douglas,

Your answer confirmed exactly the issue I have been running into and cleared
up a lot of confusion. Your additional comment about the "square block" was
another of the concerns that is now clear as daylight.

Thanks again for your insight and responsiveness.

MJ

Yes, the order makes a difference. It must be Chr(13) & Chr(10).

It's possible for there to be only one of the two characters, but in Access
it'll show up as a square block rather than a new line.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Sorry it has taken this long to get back to this issue. Your script has
been helpful, but I have some other questions along this line.

Does the order of the ascii characters make a difference:
Chr(13) & Chr(10);
Chr(10) & Chr(13); OR does it make any difference?

Furthermore should I be checking for the "line feed/new line" [Chr(10)]
and
"carriage return" [Chr(13)] separately as well?

Is it possible for them to show up by themselves within memos fields
rather
than paired?

Thanks for your assistance.
MJ

UPDATE MyTable
SET MyMemoField = Replace(MyMemoField, Chr(13) & Chr(10), " ")
WHERE InStr(MyMemoField, Chr(13) & Chr(10)) > 0


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My challenge is two-fold, I need to ...

1) Identify the number of records within a database table where two
memo
fields contain entries with carriage returns in the memos; and

2) Replace the carriage returns with a space.

Thank you in adavance for your assistance.

MJ

 

[MJ]

One last point of clarification, your earlier query to replace the Chr(13) &
Chr(10) with a " ", will this replace ALL of them within a record field or
just the first occurance? If only the first occurance, then is there a
formula or loop to catch multiple occurances within a given record.field?

MJ

Yes, the order makes a difference. It must be Chr(13) & Chr(10).

It's possible for there to be only one of the two characters, but in Access
it'll show up as a square block rather than a new line.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Sorry it has taken this long to get back to this issue. Your script has
been helpful, but I have some other questions along this line.

Does the order of the ascii characters make a difference:
Chr(13) & Chr(10);
Chr(10) & Chr(13); OR does it make any difference?

Furthermore should I be checking for the "line feed/new line" [Chr(10)]
and
"carriage return" [Chr(13)] separately as well?

Is it possible for them to show up by themselves within memos fields
rather
than paired?

Thanks for your assistance.
MJ

UPDATE MyTable
SET MyMemoField = Replace(MyMemoField, Chr(13) & Chr(10), " ")
WHERE InStr(MyMemoField, Chr(13) & Chr(10)) > 0


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My challenge is two-fold, I need to ...

1) Identify the number of records within a database table where two
memo
fields contain entries with carriage returns in the memos; and

2) Replace the carriage returns with a space.

Thank you in adavance for your assistance.

MJ

 

[Douglas J. Steele]

As written in my reply, the Replace function will replace all occurrences.

Check http://msdn2.microsoft.com/en-us/library/aa241892.aspx for the syntax
to be able to have it only replace some occurrences.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

One last point of clarification, your earlier query to replace the Chr(13)
&
Chr(10) with a " ", will this replace ALL of them within a record field or
just the first occurance? If only the first occurance, then is there a
formula or loop to catch multiple occurances within a given record.field?

MJ

Yes, the order makes a difference. It must be Chr(13) & Chr(10).

It's possible for there to be only one of the two characters, but in
Access
it'll show up as a square block rather than a new line.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Sorry it has taken this long to get back to this issue. Your script
has
been helpful, but I have some other questions along this line.

Does the order of the ascii characters make a difference:
Chr(13) & Chr(10);
Chr(10) & Chr(13); OR does it make any difference?

Furthermore should I be checking for the "line feed/new line" [Chr(10)]
and
"carriage return" [Chr(13)] separately as well?

Is it possible for them to show up by themselves within memos fields
rather
than paired?

Thanks for your assistance.
MJ

:

UPDATE MyTable
SET MyMemoField = Replace(MyMemoField, Chr(13) & Chr(10), " ")
WHERE InStr(MyMemoField, Chr(13) & Chr(10)) > 0


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My challenge is two-fold, I need to ...

1) Identify the number of records within a database table where two
memo
fields contain entries with carriage returns in the memos; and

2) Replace the carriage returns with a space.

Thank you in adavance for your assistance.

MJ