How to replace CRLF in Access 97 ?

P

Peter

Hi,

Access 97 is connected to SQL Server 2000 database. There
is a field that contains CRLF.

Is there any way can I replace the CRLF with space in
Access 97. Someone suggest using the REPLACE function but
it is not available in Access 97.

Thanks
 
J

John Vinson

Hi,

Access 97 is connected to SQL Server 2000 database. There
is a field that contains CRLF.

Is there any way can I replace the CRLF with space in
Access 97. Someone suggest using the REPLACE function but
it is not available in Access 97.

Thanks
Click to expand...

I'd suggest an Update query, updating the field to

Left([memofield], InStr([memofield], Chr(13) & Chr(10)) - 2) & " " &
Mid([memofield], InStr([memofield], Chr(13) & Chr(10)) + 2)

with a criterion on the field of

LIKE "*" & Chr(13) & Chr(10) & "*"

You may need to run this more than once if there are multiple CRLF's
in each memo field.

Alternatively you could write (or find) your own custom Replace() VBA
function.

John W. Vinson[MVP]
 
B

Brendan Reynolds

John Vinson said:
Is there any way can I replace the CRLF with space in
Access 97. Someone suggest using the REPLACE function but
it is not available in Access 97.
Click to expand...
I'd suggest an Update query, updating the field to
Left([memofield], InStr([memofield], Chr(13) & Chr(10)) - 2) & " " &
Mid([memofield], InStr([memofield], Chr(13) & Chr(10)) + 2)
with a criterion on the field of
LIKE "*" & Chr(13) & Chr(10) & "*"
You may need to run this more than once if there are multiple CRLF's
in each memo field.
Alternatively you could write (or find) your own custom Replace() VBA
function.
Click to expand...

There's one at the following URL ...
http://www.mvps.org/access/strings/str0004.htm
 
Top