URGENT! Problem with carriage return & square characters!

[dabooj]

Hi everyone,

I have a problem, which many people seemed to have had previously, but with a
bit of a twist.
On importing information into Access tables i get a square character instead
of a carriage return.
I found partial solutions in:

UPDATE Tablename
SET FieldName = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1, 1);

&

http://support.microsoft.com/default.aspx?scid=kb;en-us;210372

Both of these work if the data imported has the square characters instead of
the carriage return.
The problem is that i have data where some of the rows have the incorrect
data with square characters in it and some rows of data which are perfectly
fine.
Both the methods above distort the correct data into incorrect data (i.e add
in the square characters).

Is there anyway to distiguish between the correct data & the data with the
square characters? The square character shows up as ascii no 13 when i try to
identify it using asc().

Please let me know if you have any suggestions.

Thanks in advance,

Dabooj.

 

[Klatuu]

You can detect it with:
If Instr(strSuspectData, chr(13)) > 0 Then
'Bad Stuff found
End If

As you already posted, the Replace function is great for this. If, rather
than replace it with something else, you just want to take it out:

strGoodData = Replace(strSus

 

[Klatuu]

Opps, did hit post accidently. To continue:

strGoodData = Replace(strSuspectData, chr(13), "")

That will remove all the Chr(13) with a zero length string.