Data Validation - no symbols ( & % " ' *)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I validate the data in an unbound textbox to not have any special
charactors but allow alpha-numeric?
 
Easiest done with an Input Mask (in the properties for the control). Look at
Help but you could use A, a, & or C depending on whether you want to specify
how long the entry is or if you want to allow spaces.
 
You could use an Input Mask, but I don't recommend them. Here is a function
that returns False if the passed string contains no symbols and true if it
does. You can use it in the Before Update event of the textbox and modify it
if there are characters you want to include or exclude:

Private Sub MyTextBox_BeforeUpdate(Cancel As Integer)

If BadChars(Me.MyTextBox) Then
MsbBox "Invalid characters entered"
Me.MyTextBox.Undo
Cancel = True
End If

Function BadChars(strSuspect As String) As Boolean
Const conNotGood As String = """!@#$%^&*()_-+={[}]|\:;'?/>.<,`~"
Dim intCtr As Integer

For intCtr = 1 To 32
If InStr(strSuspect, Mid(conNotGood, intCtr, 1)) <> 0 Then
BadChars = True
Exit For
End If
Next intCtr

End Function
 
Klatuu,
That is exactly what I was looking to do.
I might have something wrong though, no matter what is entered into the
textbox (which really is an unbound combobox that is not limited to a list),
I get the msgbox.
I discoved the need for this validation when "&" was entered in the text
because the after update event will run a dlookup to check if the discription
exists and if it doesn't it runs a SQL statement to update a table.
Here is the code that I used from your response after I updated it.

Private Sub cmbDescription_BeforeUpdate(Cancel As Integer)

If BadChars(Me.cmbDescription) Then
MsgBox "Descriptions cannot contain special characters" _
& vbCrLf & "Example: !@#$%&*?><", , "MKTTS - No Special Characters"

Me.cmbDescription.Undo
Cancel = True
End If

End Sub

Function BadChars(strSuspect As String) As Boolean
Const conNotGood As String = "!@#$%^*&()_-?><{}][\"
Dim intCtr As Integer

For intCtr = 1 To 32
If InStr(strSuspect, Mid(conNotGood, intCtr, 1)) <> 0 Then
BadChars = True
Exit For
End If
Next intCtr

End Function



Klatuu said:
You could use an Input Mask, but I don't recommend them. Here is a function
that returns False if the passed string contains no symbols and true if it
does. You can use it in the Before Update event of the textbox and modify it
if there are characters you want to include or exclude:

Private Sub MyTextBox_BeforeUpdate(Cancel As Integer)

If BadChars(Me.MyTextBox) Then
MsbBox "Invalid characters entered"
Me.MyTextBox.Undo
Cancel = True
End If

Function BadChars(strSuspect As String) As Boolean
Const conNotGood As String = """!@#$%^&*()_-+={[}]|\:;'?/>.<,`~"
Dim intCtr As Integer

For intCtr = 1 To 32
If InStr(strSuspect, Mid(conNotGood, intCtr, 1)) <> 0 Then
BadChars = True
Exit For
End If
Next intCtr

End Function


TimT said:
How can I validate the data in an unbound textbox to not have any special
charactors but allow alpha-numeric?
Click to expand...
Click to expand...
 
You changed the list of characters without changing the upper limit of the
For Next loop. What is happening is when intCtr becomes 21, the Mid()
function is returning a zerolength string (''"). The Instr() function is
then returning a 1 which causes the function to return True.

Believe it or not, InStr("ABCDEFG", "") returns 1, not 0.

So, intCtr should be the number of characters in conNotGood. Happens to be
20 if the code you sent is complete. You took some out.

TimT said:
Klatuu,
That is exactly what I was looking to do.
I might have something wrong though, no matter what is entered into the
textbox (which really is an unbound combobox that is not limited to a list),
I get the msgbox.
I discoved the need for this validation when "&" was entered in the text
because the after update event will run a dlookup to check if the discription
exists and if it doesn't it runs a SQL statement to update a table.
Here is the code that I used from your response after I updated it.

Private Sub cmbDescription_BeforeUpdate(Cancel As Integer)

If BadChars(Me.cmbDescription) Then
MsgBox "Descriptions cannot contain special characters" _
& vbCrLf & "Example: !@#$%&*?><", , "MKTTS - No Special Characters"

Me.cmbDescription.Undo
Cancel = True
End If

End Sub

Function BadChars(strSuspect As String) As Boolean
Const conNotGood As String = "!@#$%^*&()_-?><{}][\"
Dim intCtr As Integer

For intCtr = 1 To 32
If InStr(strSuspect, Mid(conNotGood, intCtr, 1)) <> 0 Then
BadChars = True
Exit For
End If
Next intCtr

End Function



Klatuu said:
You could use an Input Mask, but I don't recommend them. Here is a function
that returns False if the passed string contains no symbols and true if it
does. You can use it in the Before Update event of the textbox and modify it
if there are characters you want to include or exclude:

Private Sub MyTextBox_BeforeUpdate(Cancel As Integer)

If BadChars(Me.MyTextBox) Then
MsbBox "Invalid characters entered"
Me.MyTextBox.Undo
Cancel = True
End If

Function BadChars(strSuspect As String) As Boolean
Const conNotGood As String = """!@#$%^&*()_-+={[}]|\:;'?/>.<,`~"
Dim intCtr As Integer

For intCtr = 1 To 32
If InStr(strSuspect, Mid(conNotGood, intCtr, 1)) <> 0 Then
BadChars = True
Exit For
End If
Next intCtr

End Function


TimT said:
How can I validate the data in an unbound textbox to not have any special
charactors but allow alpha-numeric?
Click to expand...
Click to expand...
Click to expand...
 
Hi Tim,

Use a validation rule like this:

(Not Like "*[!0-9A-Za-z]*") Or Is Null

If spaces or underscores are allowed, include them after the !.
 
Back
Top