I have this cool little form in an Access app that allows the user to use sort of a "Thumb Index" to filter a large file of customers. There are 27 buttons - one for each letter, and one with a pound sign on it for customers with names like "123 Plumbing" or "1-800-Ask Gary."
Getting the sql to filter on the first letter is easy. But to get all the clients whose names start with a non-letter, I used this:
If strAlpha = "#" Then 'Number Picked
strCritString = " WHERE InStr('ABCDEFGHIJKLMNOPQRSTUVWXYZ', UCase(Left([Accounts]![Customer],1))) = 0 "
Else 'Letter Picked
strCritString = " WHERE UCase(Left([Accounts]![Customer],1))='" & strAlpha & "'"
Evidently the InStr function in VB 2005 functions a little differently. I just want to be able to test for the case where the "strAlpha" variable is not one of the 26 letters.