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

Mark LaGrange
Mark LaGrange used Ask the Experts™
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?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

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

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


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?


Ok, Right(MyMemoField,8) pulls what I expect, but InStr(Right(MyMemoField,8),Chr$(3804)) still gets "Invalid procedure call"
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

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


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...
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
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 :)


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


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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial