help needed with a query or filter

[smichael]

Is there any way I can query or filter to find records that may be entered in all caps?

Also, is there any way I can query or filter or find/replace records containing the # sign? Traditionally this symbol is recognized as a wildcard.

 

[KenG]

Try this function to look for caps (copy and paste it into a module) in your query

Function FindAllCaps(str As String) As Boolean
Dim i As Long
i = 1
Do Until i = Len(str)
If Asc(Mid$(str, i, 1)) < 65 Or Asc(Mid$(str, i, 1)) > 90 Then
FindAllCaps = False
Exit Function
End If
i = i + 1
Loop
FindAllCaps = True
End Function

To look for the '#' modify the above function to use Asc(35)

Let me know if this is what you're looking for, or if you want to know what's going on in the Function.

 

[smichael]

Thanks for the code. I'm a real beginner. Once I've copied it into a module, what do I do with it? How do Include it in my query?

 

[KenG]

Ooops!
I found a problem with the function I sent you (remember you get what you paid for ;-)
Try this function instead;
Function FindAllCaps(str As String) As Boolean
Dim i As Long
i = 1
Do
If Asc(Mid$(str, i, 1)) < 65 Or Asc(Mid$(str, i, 1)) > 90 Then
FindAllCaps = False
Exit Function
End If
i = i + 1
Loop Until i = Len(str) + 1
FindAllCaps = True
End Function

Anyway...
In the first row of the design grid called &quot;Field:&quot; put something like this;
AllCaps:FindAllCaps([YourTable].[FieldThatHasCaps])

You are creating a new field in this query called &quot;AllCaps&quot; that uses the Function &quot;FindAllCaps&quot;. The function is looking for caps in the data from the field &quot;FieldThatHasCaps&quot; in the table called &quot;YourTable&quot;

Then in the criteria row put the word True.

Now only records that return True from the function are shown.

Also, put any other fields you might want to see.


To understand the function, look up the keywords Asc, Mid, and ASCII in Help.

One word of caution, this function is fairly raw. It'll return errors if the field contains Null values. Also, there will be no caps found if there are spaces or numbers in the data.

Let me know if this helps.

 

[smichael]

That works! Thank you for your help.