CODEWITHSUNDEEP
sqlms-accessascii
user2450064
user2450064

Reputation: 113

How to query a field with ASCII 13 or "Carriage return"?

I Imported an XML file into a table.Then I tried to query the field that are not null. I have a field which seems to be blank or null but still returned in the query.

I'm not sure if this is newline but upon searching the net it is what they called "Carriage return"

the part of the imported XML looks like this:

<Myfield>
</Myfield>

I tried to query it it's not pulled in the ff:

Where Myfield <> null

Where Myfield <> ""

I tried to select it using ASC() function and it returns 13 but when trying to filter it by this code below, I am getting "Invalid Procedure Call" error

SELECT * FROM Table1 where asc(Myfield) = '13'

Upvotes: 2

Views: 10365

Answers (2)

HansUp
HansUp

Reputation: 97101

Perhaps what you're seeing is actually CRLF, a carriage return (ASCII 13) plus linefeed (ASCII 10), which is actually 2 characters. It should be easy to check ...

SELECT * FROM Table1 WHERE Myfield = Chr(13) & Chr(10)

Just in case the imported XML brought in any spaces or other non-printing characters, you could check for CRLF anywhere in the field.

SELECT * FROM Table1 WHERE Myfield ALike '%' & Chr(13) & Chr(10) '%'

ALike differs from Like in that it signals the db engine to expect ANSI wild cards, % and _, instead of Access' * and ?

Upvotes: 4

Andy G
Andy G

Reputation: 19367

SELECT * FROM Table1 where Myfield = Chr(13)

I might also look for a limited number of characters

SELECT * FROM Table1 WHERE Myfield = Chr(13) OR Len(Myfield) < 2    ' or < 3

Upvotes: 0

Related Questions