Using Validation Rule

[smay]

I currently use Access to maintain an mailing list. Bulk mailing address
requirements only allow capital letters, no punctuation or other characters.
I have limited the capitalization in the function.

I would like to use the validation rule below. The problem is it does not
allow the use of space between text as needed


Rule: Is Null Or Not Like "*[!((a-z) or (0-9))]*"
Accepts A to Z and 0 to 9, but no punctuation or other characters.

 

[Guest]

Here is a function that will perform that test. It allows only uppercase
characters, digits, and spaces. It returns True if the string passes the
test and false if it does not.

Public Function CapsAndNumsOnly(ByVal strTest As String) As Boolean
Dim lngCtr As Long
Dim lngLength As Long
Dim intAscii As Integer

lngLength = Len(strTest)
For lngCtr = 1 To lngLength
intAscii = Asc(Mid(strTest, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii = 32) Then
CapsAndNumsOnly = True
Else
CapsAndNumsOnly = False
Exit For
End If
Next lngCtr

End Function

Now, to use it, call it in the Before Update event of the text box where you
enter it:

If Not CapsAndNumsOnly(Me.txtSomeContro) Then
MsgBox "Invalid Characters In String", vbExclamation
Cancel = True
End If

 

[smay via AccessMonster.com]

Thanks for your help.
I tried this in the Properties-Validation Rule box. I get an "invalid syntax"
error message. Unfortunately I have no experience writing expressions so I'm
not sure what needs to be corrected.

Here is a function that will perform that test. It allows only uppercase
characters, digits, and spaces. It returns True if the string passes the
test and false if it does not.

Public Function CapsAndNumsOnly(ByVal strTest As String) As Boolean
Dim lngCtr As Long
Dim lngLength As Long
Dim intAscii As Integer

lngLength = Len(strTest)
For lngCtr = 1 To lngLength
intAscii = Asc(Mid(strTest, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii = 32) Then
CapsAndNumsOnly = True
Else
CapsAndNumsOnly = False
Exit For
End If
Next lngCtr

End Function

Now, to use it, call it in the Before Update event of the text box where you
enter it:

If Not CapsAndNumsOnly(Me.txtSomeContro) Then
MsgBox "Invalid Characters In String", vbExclamation
Cancel = True
End If

I currently use Access to maintain an mailing list. Bulk mailing address
requirements only allow capital letters, no punctuation or other characters.

[quoted text clipped - 6 lines]

Rule: Is Null Or Not Like "*[!((a-z) or (0-9))]*"
Accepts A to Z and 0 to 9, but no punctuation or other characters.

 

[Guest]

Re read my previous post. This does not belong in the validation rule. I
don't use validation rules.

Put the code in the form module.

--
Dave Hargis, Microsoft Access MVP

Thanks for your help.
I tried this in the Properties-Validation Rule box. I get an "invalid syntax"
error message. Unfortunately I have no experience writing expressions so I'm
not sure what needs to be corrected.

Here is a function that will perform that test. It allows only uppercase
characters, digits, and spaces. It returns True if the string passes the
test and false if it does not.

Public Function CapsAndNumsOnly(ByVal strTest As String) As Boolean
Dim lngCtr As Long
Dim lngLength As Long
Dim intAscii As Integer

lngLength = Len(strTest)
For lngCtr = 1 To lngLength
intAscii = Asc(Mid(strTest, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii = 32) Then
CapsAndNumsOnly = True
Else
CapsAndNumsOnly = False
Exit For
End If
Next lngCtr

End Function

Now, to use it, call it in the Before Update event of the text box where you
enter it:

If Not CapsAndNumsOnly(Me.txtSomeContro) Then
MsgBox "Invalid Characters In String", vbExclamation
Cancel = True
End If

I currently use Access to maintain an mailing list. Bulk mailing address
requirements only allow capital letters, no punctuation or other characters.

[quoted text clipped - 6 lines]

Rule: Is Null Or Not Like "*[!((a-z) or (0-9))]*"
Accepts A to Z and 0 to 9, but no punctuation or other characters.

 

[RoyVidar]

I currently use Access to maintain an mailing list. Bulk mailing
address requirements only allow capital letters, no punctuation or
other characters. I have limited the capitalization in the function.

I would like to use the validation rule below. The problem is it does
not allow the use of space between text as needed


Rule: Is Null Or Not Like "*[!((a-z) or (0-9))]*"
Accepts A to Z and 0 to 9, but no punctuation or other characters.

On my setup, that test does allow spaces between words, but it also
allows parenthesis (). Have you tried something like this

Is Null Or Not Like "*[!a-z 0-9]*"

If this is at table level, and there's any chance you'll be adding data
to the table through ADO, I'd recommend one step further

Is Null Or (Not Like "*[!a-z 0-9]*" And Not Like "%[!a-z 0-9]%")

which is taking into consideration the ADO wildchard character '%'