asked on

How to search for the way-out-there ASCII characters?

Hello - how can I search in a query for ASCII characters outside the realm of alpha, numbers, punctuation, and the usual special characters, like in the attached pic?

Thanks
Strange-ASCII-1.jpg

Dale Fye

you should be able to use the instr() function, inconjunction with chr$()

WHERE Instr([yourField], chr$(AsciiValue)) > 0

Mark LaGrange

ASKER

Well, foof... I should have been more clear. My original question was more "How do I know what character # that is, but now my brain fart has passed, and I see it is 3804.

But now I'm trying "WHERE InStr([MyField], Chr$(3804)) > 0", and getting "Invalid Procedure Call".

MyField is a Memo field; is that allowed with the InStr() function?

Mark LaGrange

ASKER

Ok, Right(MyMemoField,8) pulls what I expect, but InStr(Right(MyMemoField,8),Chr$(3804)) still gets "Invalid procedure call"

Anders Ebro (Microsoft MVP)

If you know it to be a unicode character, you can use chrW instead of chr.

Mark LaGrange

ASKER

It's the Chr$(3804). I changed to Chr$(64) (the "@" character, which also appears in this particular field), and it works.
I don't know what you call that range of ASCII codes; it's just way out there...

ASKER CERTIFIED SOLUTION

Anders Ebro (Microsoft MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark LaGrange

ASKER

Works like a charm - Thank You, Sir!

One curious thing - no matter what the length of the string, it shows the position of the 1st non-ASCII at 5900. Is that just what you would expect with a Memo-type field, or does that suggest something else to you?

Thanks again
C--Users-Mark-Desktop-first-non-asci.png

Mark LaGrange

ASKER

Thanks!

Anders Ebro (Microsoft MVP)

Your welcome. Its not returning the position, but the AscW value of the character. AscW is basically the reverse of ChrW. That value can then be used e.g. with a replace statement if you need to remove the character.