How to replace CRLF in Access 97 ?

[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

 

[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

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]

 

[Brendan Reynolds]

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.

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.

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