Limit a field to only Alpha Numeric

[Adam McReynolds]

I would like to know how to limit a field on a form to only Alpha Numeric characters.

Example: ~AAUZNTO

This would be scanned by a bar code and I want the field to show only this when scanned: AAUZNTO

Any help would be awesome. Thank you.

 

[DavidAtWork]

you could write a small function which you pass the scanned string and it removes unwanted characters and sends back the remainder. I would have thought there was a vba function that would do this, but I can't find anything. Try this

Function cleanStr(incomingStr as String) As String
Dim i, j as Integer
Dim tempStr as String
 
i = Len(incomingStr)
tempStr = ""
 
For j = 1 To i
    Select Case Asc(Mid(incomingStr, j, 1))
        Case 48 To 57: tempStr = tempStr & Mid(incomingStr, j, 1)
        Case 65 To 90: tempStr = tempStr & Mid(incomingStr, j, 1)
        Case 97 To 122: tempStr = tempStr & Mid(incomingStr, j, 1)
        Case Else: tempStr = tempStr
    End Select
Next j
cleanStr = tempStr
End function

David

 

[Adam McReynolds]

you could write a small function which you pass the scanned string and it removes unwanted characters and sends back the remainder. I would have thought there was a vba function that would do this, but I can't find anything. Try this

Function cleanStr(incomingStr as String) As String
Dim i, j as Integer
Dim tempStr as String
 
i = Len(incomingStr)
tempStr = ""
 
For j = 1 To i
    Select Case Asc(Mid(incomingStr, j, 1))
        Case 48 To 57: tempStr = tempStr & Mid(incomingStr, j, 1)
        Case 65 To 90: tempStr = tempStr & Mid(incomingStr, j, 1)
        Case 97 To 122: tempStr = tempStr & Mid(incomingStr, j, 1)
        Case Else: tempStr = tempStr
    End Select
Next j
cleanStr = tempStr
End function

David

Thanks for the reply. I have been busy so I haven't had a chance to get to this one yet. Where would I place this code? It seems to me to be outside of the private sub. Thanks again for all the help.

 

[missinglinq]

Place the code in a Standard Module.

When asked, name the Module anything except cleanStr. When a Function and the Module it resides in has the same name it confuses the Access Gnomes no end, and they throw fits as well as errors!

To use it, place this in the AfterUpdate event of the Textbox in question; we'll call it TargetTextbox, in this code;

Private Sub TargetTextbox_AfterUpdate()
 Me.TargetTextbox = cleanStr(Me.TargetTextbox)
End Sub

Linq ;0)>

 

[DavidAtWork]

Adam, yes as Ling has described above, if it errors on the function call (pass value by reference), try

Me.TargetTextbox = cleanStr CStr(Me.TargetTextbox)

David

 

[missinglinq]

[B]Me.TargetTextbox = cleanStr CStr(Me.TargetTextbox)[/B]

FYI, David:

The values are AlphaNumeric, as stated in the thread title, so there's no need for CStr(). It runs just fine using

Me.TargetTextbox = cleanStr(Me.TargetTextbox)

If you did need to use it, it would have to be

Me.TargetTextbox = cleanStr(CStr(Me.TargetTextbox))

not

Me.TargetTextbox = cleanStr CStr(Me.TargetTextbox)

Linq ;0)>

 

[Adam McReynolds]

Adam, yes as Ling has described above, if it errors on the function call (pass value by reference), try

Me.TargetTextbox = cleanStr CStr(Me.TargetTextbox)

David

Thank you so much! It works!!! Finally I don't have to go through each record and delete these(~) ugly little things.

 

[missinglinq]

Glad we could help!

Linq ;0)>

 

[Adam McReynolds]

Is there a way to limit just the outside of the string to alpha numeric but leave the middle of the string as is?

 

[TJPoorman]

If the only place the ~ symbol is is on the left you could use this:

Private Sub TargetTextbox_AfterUpdate()
    If Left(Me.TargetTextbox,1) = "~" Then Me.TargetTextbox = Mid(Me.TargetTextbox,2)
End Sub

 

[Adam McReynolds]

If the only place the ~ symbol is is on the left you could use this:

Private Sub TargetTextbox_AfterUpdate()
    If Left(Me.TargetTextbox,1) = "~" Then Me.TargetTextbox = Mid(Me.TargetTextbox,2)
End Sub

Thanks for the reply. Either the left or the right. The left usually(~) and the right(-). They are serial numbers and they come from many different mfg. and are all a little different. My issue is that with a certain mfg. the have (-) in the middle and some annoying person here at work is worried about my warranty code not finding older instances. Thanks again for the help.

 

[missinglinq]

So you need to allow non-AlphaNumeric characters if they are not the first or last character in the original string?

Linq ;0)>

 

[Cronk]

I notice there is no provision being made for a blank in the text box.

 

[DavidAtWork]

The original function works by inspecting each character in the string and the postion of each character, so conditional logic could be applied to the char position and allow certain characters according to position, but you'd need to know all the conditions where certain characters would be allowed

David

 

[missinglinq]

...So you need to allow non-AlphaNumeric characters if they are not the first or last character in the original string...

If the above is true, here's code that only checks the First and Last characters and strips them if they are not AlphaNumeric. Any Non-AlphaNumeric characters in the middle of the string are left in place:

Public Function CheckFirstLast4AlphaNumeric(strValue)

Dim strReturn As String

[COLOR="Red"]'Strips everything except 0-9 and A-Z in First and Last Characters
[/COLOR]

If IsNull(strValue) Then Exit Function

[COLOR="Red"]'Check First Character/Strip if Not AlphaNumeric[/COLOR]

If Not Left(strValue, 1) Like "[0-9A-Z]" Then
  strReturn = Mid(strValue, 2)
Else
  strReturn = strValue
End If

[COLOR="Red"]'Check Last Character/Strip if Not AlphaNumeric[/COLOR]

If Not Right(strReturn, 1) Like "[0-9A-Z]" Then
  strReturn = Left(strReturn, Len(strReturn) - 1)
Else
  strReturn = strReturn
End If

CheckFirstLast4AlphaNumeric = strReturn


End Function

Then call the Function like this:

Private Sub TargetControl_AfterUpdate()
  Me.TargetControl = CheckFirstLast4AlphaNumeric(Me.TargetControl)
End Sub

Linq ;0)>

 

[Adam McReynolds]

If the above is true, here's code that only checks the First and Last characters and strips them if they are not AlphaNumeric. Any Non-AlphaNumeric characters in the middle of the string are left in place:

Alright, got back into to work to test this. It works great! Thank you soooo much. Now I can tell that annoying co-worker to eat it. Haha. Cheers!

 

[missinglinq]

Programming would be a great job...if it weren't for the end users!

Once again, glad we could help!

Linq ;0)>

 

[DavidAtWork]

If you use Ling's method to check first/last characters, do you not need to declarer the function as:

Public Function CheckFirstLast4AlphaNumeric(strValue) As String

without the 'As String' I wouldn't expect it to return a value

David

 

[missinglinq]

...without the 'As String' I wouldn't expect it to return a value...

And yet, amazingly, it does!

The [As Type] argument is optional.

Linq ;0)>