Querying for alphabetic characters

[jonathon]

Hi

I suspect the answer to this is staring me in the face, but as a newbie to Access I'm flummoxed:

I have a table with a field of 'words' (one word per record), some of which actually have numbers, hyphens and other special characters in (e.g. *,&,@, etc). I would like to extract those words which only contain alphabetic characters (i.e. a-z). How do I go about specifying this in the criteria box of the query form? I know [a-z] will look for the right characters but how do I apply that to every character in the record?

Any help greatly appreciated.

 

[Mile-O]

Firstly, copy this code and put it in a module.

Public Function TestCharacters(ByVal strValue As String) As Boolean
    
    On Error Goto Err_TestCharacters
    
    Dim intCounter As Integer ' create a loop counter
    
    For intCounter = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intCounter, 1))
            Case Is > 64, < 123
                TestCharacters = True
            Case Else
                TestCharacters = False
                Exit Function
        End Select
    Next intCounter

Exit_TestCharacters:
    Exit Function

Err_TestCharacters:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_TestCharacters

End Function

Secondly, in your query create a new calculated field like this:

NewField: TestCharacters([YourTextField])

Don't make it visible and set its criteria to True.


That should do it for you.

 

[jonathon]

Thanks for the quick response!

I had a problem with your code in that it gave a result of "true" for every record, even those with &,@,% etc, but I was able to change the code to something that worked (changed the case is >64, is <123 to ="a" etc) - I'm sure this is totally inefficient, but ultimately it got the job done. Without the code above I would have been lost.

 

[Mile-O]

My mistake (as usual ) - I've amended it now.

 

[raskew]

Gotta do something about chr(91) - chr(96)

Function SaveAlpha(pStr As String) As String
'*******************************************
'Name:      SaveAlpha (Function)
'Purpose:   Removes non-alpha characters from
'           a string
'Calls:     Function IsAlpha()
'Inputs:    ? SaveAlpha(" t#he *qu^i5ck !b@r#o$w&n fo#x ")
'Output:    the quick brown fox
'Note:      As written, empty spaces are ignored.
'*******************************************

Dim strHold As String
Dim intLen As Integer, n As Integer

strHold = Trim(pStr)
intLen = Len(strHold)
n = 1
Do
   If Mid(strHold, n, 1) <> " " And Not IsAlpha(Mid(strHold, n, 1)) Then
      strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
      n = n - 1
   End If
   n = n + 1
Loop Until Mid(strHold, n, 1) = ""
SaveAlpha = strHold
End Function 

Function IsAlpha(strIn As String) As Boolean
'*******************************************
'Name:      IsAlpha (Function)
'Purpose:   Determine if a character is alpha
'           i.e. "a" - "z" or "A" - "Z"
'Inputs:    ? IsAlpha("4"),
'Output:    False
'*******************************************

Dim I As Integer
I = Switch(Asc(strIn) > 122 Or Asc(strIn) < 65, 1, _
    InStr("91 92 93 94 95 96", Asc(strIn)) > 0, 2, _
    True, 3)
IsAlpha = IIf(I = 3, True, False)
End Function

 

[doran_doran]

amendet module

Mile o phile said, he amended the mod file. where is the modified file. b/c the current one gives me all the record.

 

[Mile-O]

This is an oldie. My mistake for never fixing this: Here you go:

Public Function TestCharacters(ByVal strValue As String) As Boolean
    
    On Error GoTo Err_TestCharacters
    
    Dim intCounter As Integer ' create a loop counter
    
    For intCounter = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intCounter, 1))
            Case Is < 64, Is > 123, 91, 92, 93, 94, 95, 96
                TestCharacters = False
                Exit Function
            Case Else
                TestCharacters = True
            End Select
    Next intCounter

Exit_TestCharacters:
    Exit Function

Err_TestCharacters:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_TestCharacters

End Function

 

[klchoy]

How about using Like "*[a-z]*" in the criteria row within query? That should fix it.

 

[Mile-O]

Because that could return a record such as: @@@@###6565764Smith5435~[][]8656