Avatar of Mark LaGrange
Mark LaGrange

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
Microsoft Access

Avatar of undefined
Last Comment
Anders Ebro (Microsoft MVP)
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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

WHERE Instr([yourField], chr$(AsciiValue)) > 0
Avatar of Mark LaGrange
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?
Avatar of Mark LaGrange
Mark LaGrange

ASKER

Ok, Right(MyMemoField,8) pulls what I expect, but InStr(Right(MyMemoField,8),Chr$(3804)) still gets "Invalid procedure call"
If you know it to be a unicode character, you can use chrW instead of chr.
Avatar of Mark LaGrange
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
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Mark LaGrange
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
Avatar of Mark LaGrange
Mark LaGrange

ASKER

Thanks!
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.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo