Validation Rule: Only Alphanumeric

[chconnol]

I know that I could have done a better effort searching the forums and other places but you'd think that the answer to this would be easy: how do you set a Validation Rule for a field where a person can only enter an alphanumeric value?

I'm setting up an invoice tracking database and one of the recurring problems end users have is the inconsistent entering of an invoice number with symbols. We want to eliminate this and have them enter ONLY alphanumeric values. In other systems I've used, this has been easy to setup. But not in Access. Can someone advise? I've been trying to deal with this for two days.

Thanks...

 

[RuralGuy]

See if these two functions in a standard module help. In the BeforeUpdate event of your control put:
Me.YourControlName = FilterString(Me.YourControlName)

Public Function FilterString(strIn As String) As String
'-- Filter all but alphanumeric characters from strIn
   Dim Marker As Long
   If Len(strIn & "") > 0 Then
      For Marker = 1 To Len(strIn)
         FilterString = FilterString & FilterIt(Mid(strIn, Marker, 1))
      Next Marker
   Else
      '-- Do not attempt any conversion
      MsgBox "Invalid entry for 'FilterString' filter", vbExclamation + vbOKOnly
   End If
End Function

Public Function FilterIt(InChr As String) As String
'-- Strip all but alphanumeric characters
   Select Case InChr
      Case "a" To "z"
         FilterIt = InChr     '-- Valid character
      Case "A" To "Z"
         FilterIt = InChr     '-- Valid character
      Case "0" To "9"
         FilterIt = InChr     '-- Valid character
      Case Else
         FilterIt = ""        '-- Strip this character
   End Select
End Function

...using Your Control Name of course.

 

[chconnol]

Thanks but....

Thanks for replying but I'm a real novice and I'm not sure at all where exactly I'm supposed to place this. Yes, I know about the BeforeEvent spot on field properties and such. But I just don't know all the elements that you're talking about....

 

[RuralGuy]

Start by copying the code and pasting it into a standard module named basStrings. The code is now available anywhere. You need to create a form to Add/Edit records in your table and call the function I recommended from the BeforeUpdate event of the control that is bound to your InvoiceNumber field. Post back is you want more detailed directions and we can take it a step at a time.

 

[Dennisk]

you could use an input mask. No code involved.

 

[RuralGuy]

Dennisk,
I'm real weak on input masks. What would the mask be?

 

[neileg]

'A' or 'a' allow only alphanumerics. 'A' is mandatory, 'a' is optional. So if you want a field that has at least one alphanumeric with up to 10 characters allowed, the mask would be
Aaaaaaaaaa

 

[RuralGuy]

And if you do not know the length?

 

[neileg]

In theory you would want to accomodate an indeterminate length, but in practice you would have a good idea of the maximum number of characters. I can't see an invoice number ever exceeding 20 characters, for example.

 

[RuralGuy]

Thanks Neil! Merry Christmas.