• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 42
  • Last Modified:

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?

Mark LaGrange
Mark LaGrange
  • 5
  • 3
1 Solution
Dale FyeCommented:
you should be able to use the instr() function, inconjunction with chr$()

WHERE Instr([yourField], chr$(AsciiValue)) > 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?
Mark LaGrangeAuthor Commented:
Ok, Right(MyMemoField,8) pulls what I expect, but InStr(Right(MyMemoField,8),Chr$(3804)) still gets "Invalid procedure call"
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
If you know it to be a unicode character, you can use chrW instead of chr.
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...
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
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 :)
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
Mark LaGrangeAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now