Only characters in field

[Ruegen]

How would you get a query to pull any field that has a character in it that isn't [A-Z] or [a-z]?



Say Julie / Smith

or

Julie # Smith

I'm hoping that I can get those fields to show that have a numeric or special character in them - I need to filter these out and prevent the staff from being able to use characters other than set letters.

[June7]

I think this would require a custom function that reads each individual character of the string and checks if it is in the alpha set. When a non-alpha is encountered the function returns True and exits.

Bing: Access VBA non-alpha

http://www.techonthenet.com/access/f...phanumeric.php

[Ruegen]

I think this would require a custom function that reads each individual character of the string and checks if it is in the alpha set. When a non-alpha is encountered the function returns True and exits.

Bing: Access VBA non-alpha

http://www.techonthenet.com/access/f...phanumeric.php

But it's the actual query - I was hoping to make an update query using something like:

like '*[A-Z]*'

or maybe include ! to have

like '*[!A-Z]*'

however this is giving me all results that are alphabet

so I tried

is not like '*[!A-Z]*' or not like '*[!a-z]*'

which pulls null fields and if I say or not like null I get nothing.

:-/

[Ruegen]

Unless of course in the query I could do

CustomQueryField: AlphaNumeric([FieldName])

then put true and have results show?

[June7]

Yes, call function from query.

[Ruegen]

Yes, call function from query.

Great this works well - I'm putting this on a control box on a key down event so that when they type an invalid character it automatically deletes the last character they type

I'm thinking that if they type say invalid character # then the event fires the key code constant vbKeyBack

Is that safe to do?

[June7]

Should be. I've never tried it.

[Ruegen]

Should be. I've never tried it.

I've made up this globally so that I can call it on any field I need - I was hoping you'd have a breeze through it and see if there is anything you would change

Public Sub CheckNameField(KeyAscii As Integer)
Dim characterSwap  As String


characterSwap = Chr(KeyAscii)




Select Case KeyAscii
Case 8
characterSwap = "a"
Debug.Print characterSwap
End Select


Debug.Print KeyAscii


If AlphaNumericName(characterSwap) = False Then
MsgBox "You can't use this character at all, please don't use special characters. The name field is STRICTLY for letter characters and the name of the person"
SendKeys Chr(8), True
End If




End Sub

[June7]

Show code for the AlphaNumericName procedure.

Or instead of calling that function:

If characterSwap Like "[!a-Z]" Then

Your code seems to work (I used my suggested change).

Ooops, might what to handle ESC and space same as backspace.
Case 8, 27, 32

Also, what about contracted (apostrophe, code 39) and hyphenated (hyphen, code 45) names?

[Ruegen]

Show code for the AlphaNumericName procedure.

Or instead of calling that function:

If characterSwap Like "[!a-Z]" Then

Your code seems to work (I used my suggested change).

Ooops, might what to handle ESC and space same as backspace.
Case 8, 27, 32

Also, what about contracted (apostrophe, code 39) and hyphenated (hyphen, code 45) names?

Function AlphaNumericName(pValue) As Boolean


   Dim LPos As Integer
   Dim LChar As String
   Dim LValid_Values As String
   
   'Start at first character in pValue
   LPos = 1
   
   'Set up values that are considered to be alphanumeric
   LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'’-"
   
   'Test each character in pValue
   While LPos <= Len(pValue)
   
      'Single character in pValue
      LChar = Mid(pValue, LPos, 1)
      
      'If character is not alphanumeric, return FALSE
      If InStr(LValid_Values, LChar) = 0 Then
         AlphaNumericName = False
         Exit Function
      End If
      
      'Increment counter
       LPos = LPos + 1
       
   Wend
    
   'Value is alphanumeric, return TRUE
   AlphaNumericName = True
    
End Function

Yes there's a few keys I tried to account for - sending delete key to equal the letter "a" was one of them (since for some reason there was no chr() for delete key and came up as a dot)

[ssanfu]

While there is not a character for the delete key, you can check for non-printable by checking the ASCII codes.

Here is an ASCII chart: http://www.asciitable.com/

DEL = ASCII key code 127
ESC = ASCII key code 27
BkSP = ASCII key code 8
TAB = ASCII key code 9

[Ruegen]

While there is not a character for the delete key, you can check for non-printable by checking the ASCII codes.

Here is an ASCII chart: http://www.asciitable.com/

DEL = ASCII key code 127
ESC = ASCII key code 27
BkSP = ASCII key code 8
TAB = ASCII key code 9

so add a case for each of these and I should be fine

[Ruegen]

Show code for the AlphaNumericName procedure.

Or instead of calling that function:

If characterSwap Like "[!a-Z]" Then

Your code seems to work (I used my suggested change).

Ooops, might what to handle ESC and space same as backspace.
Case 8, 27, 32

Also, what about contracted (apostrophe, code 39) and hyphenated (hyphen, code 45) names?

yeah most of those are covered in " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ'’-"

[Ruegen]

Thanks guys

I'll close this one off as solved