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
Mark LaGrangeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
you should be able to use the instr() function, inconjunction with chr$()

WHERE Instr([yourField], chr$(AsciiValue)) > 0
0
Mark LaGrangeAuthor Commented:
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?
0
Mark LaGrangeAuthor Commented:
Ok, Right(MyMemoField,8) pulls what I expect, but InStr(Right(MyMemoField,8),Chr$(3804)) still gets "Invalid procedure call"
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
If you know it to be a unicode character, you can use chrW instead of chr.
0
Mark LaGrangeAuthor Commented:
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...
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
So usually the issue is that we know something is there (probably imported in, or copy pasted in) but we can't seem to identify it, nor how to search for it. So this function I just wrote might help you.
Public Function list_first_non_ascii(pString As Variant) As Variant
'Variants work well with querys, especially if the input can potentially be null
   If IsNull(pString) Then Exit Function
      
   Dim i As Long
   Dim c As String
   For i = 1 To Len(pString)
      c = Mid(pString, i, 1)
      If AscW(c) > 255 Then
         list_first_non_ascii = c & "=" & AscW(c)
         Exit Function
      End If
   Next
End Function

Open in new window


You can call it like this in a query:
Query non ascii
which will return results like this:
non ascii 2
I hope that can help you :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark LaGrangeAuthor Commented:
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
0
Mark LaGrangeAuthor Commented:
Thanks!
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.