Finding carriage returns in a query

[DeRochier]

I have imported non-normalized data from Excel into Access. Some fields have carriage return at the beginning of the text string. How can I write a query to isolate them? My research indicates that carriage returns are represented in Access as Chr(13) & Chr(10), but when I put that into a query it yields no results (even though I know there are carriage returns in the data set).

SELECT tblData.LO2
FROM tblData
WHERE (((tblData.LO2) Like "*" & Chr(13) & Chr(10) & "*"));

Can I write a SELECT query to isolate the records with carriage returns?

 

[Andrzejek]

According to this article, you may try:[tt]
like "*" & chr(13) & "*"[/tt]
Or write your own (User Defined) Function where you pass the value of your field and it returns, well, whatever you want.



---- Andy

There is a great need for a sarcasm font.

 

[DeRochier]

Thank you, Andy. I had tried that but the query doesn't return zero results even though I know there are three records containing a carriage return.

 

[combo]

Are you sure that chr(13) is in the string? I did a quick test with data copied from excel, only chr(10) inside criteria string works.

combo

 

[MajP]

Like "*" & Chr(10) & "*" Or Like "*" & Chr(13) & "*"

I think you want want to check CR, LF and CRLF

Public Function CleanField(varField As Variant) As Variant
   'turn spaces and nulls to empty strings
   CleanField = varField & ""
   CleanField = Trim(CleanField)
   'Hidden characters are ASCII 0 to 32 but these are the main ones. You can google the table
   'You can loop them all except 32 since that is a space which could be inside your string
   CleanField = Replace(CleanField, Chr(9), "")  'Tab
   CleanField = Replace(CleanField, Chr(10), "") 'Line Feed
   CleanField = Replace(CleanField, Chr(13), "") 'Carriage return
   'either leave anwer as "" string or change all to null
   If CleanField = "" Then CleanField = Null
End Function

Public Function CleanField2(varField As Variant) As Variant
   'turn spaces and nulls to empty strings
   CleanField2 = varField & ""
   CleanField2 = Trim(CleanField2)
   Dim i As Integer
   'Hidden characters are ASCII 0 to 32 but these are the main ones. You can google the table
   'You can loop them all except 32 since that is a space which could be inside your string
   For i = i To 31
     CleanField2 = Replace(CleanField2, Chr(i), "")  'Tab
   Next i
   If CleanField2 = "" Then CleanField2 = Null
End Function