Validation Rule for maximum length

[Guest]

I am trying to add a validation rule in a field that should not allow a null
value and also check that only alphabets are entered. I added following
validation rule.

Is Not Null And <>"" And Like "*[A-Z]*" And Not Like "*[0-9]*"

It checks if a digit is entered and displays the validation text but does
not check for emtpy row and allows null entries.
What do I need to change in the above validation rule so both conditions are
checked.

Thanks

 

['69 Camaro]

Hi.

I am trying to add a validation rule in a field that should not allow a
null
value and also check that only alphabets are entered.

Try:

Is Not Null And Not Like "*[!A-Z]*"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I am trying to add a validation rule in a field that should not allow a
null
value and also check that only alphabets are entered. I added following
validation rule.

Is Not Null And <>"" And Like "*[A-Z]*" And Not Like "*[0-9]*"

It checks if a digit is entered and displays the validation text but does
not check for emtpy row and allows null entries.
What do I need to change in the above validation rule so both conditions
are
checked.

Thanks

 

[BruceM]

An input mask may be a simpler way of getting what you need. LLL as the
input mask would require three letters. Help has more information.
To use validation, if you set the Required property for the field to Yes and
Allow Zero Length to No, the rest of the rule:
Like "*[A-Z]*" And Not Like "*[0-9]*"
should work for the rest.

 

[Guest]

You can always set the field in the table to Allow Zero Lenght to No.

 

[Guest]

Thanks for all your suggestions. It worked.

An input mask may be a simpler way of getting what you need. LLL as the
input mask would require three letters. Help has more information.
To use validation, if you set the Required property for the field to Yes and
Allow Zero Length to No, the rest of the rule:
Like "*[A-Z]*" And Not Like "*[0-9]*"
should work for the rest.

I am trying to add a validation rule in a field that should not allow a
null
value and also check that only alphabets are entered. I added following
validation rule.

Is Not Null And <>"" And Like "*[A-Z]*" And Not Like "*[0-9]*"

It checks if a digit is entered and displays the validation text but does
not check for emtpy row and allows null entries.
What do I need to change in the above validation rule so both conditions
are
checked.

Thanks

 

[Jamie Collins]

I am trying to add avalidationrule in a field that should not allow a
null
value and also check that only alphabets are entered.

Try:

Is Not Null And Not Like "*[!A-Z]*"

Warning: the above validation rule is unsafe. To test:

Sub Test69()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"

.ActiveConnection.Execute _
"CREATE TABLE Test (" & _
"data_col NVARCHAR(255));"

' Create Validation Rules
Dim jeng
Set jeng = CreateObject("JRO.JetEngine")
jeng.RefreshCache .ActiveConnection

.Tables("Test").Columns("data_col") _
.Properties("Jet OLEDB:Column Validation Rule").value = _
"Is Not Null And Not Like ""*[!A-Z]*"""

jeng.RefreshCache .ActiveConnection

.ActiveConnection.Execute _
"INSERT INTO Test (data_col) VALUES ('123');"

Set .ActiveConnection = Nothing
End With

End Sub

This vulnerability is not limited to ADO and applies to anywhere from
which ANSI-92 Query Mode may be executed -- that would include the
Access user interface from Access2002 onwards.

I suggest a safe rule would be:

Is Not Null And Not Like "*[!A-Z]*" And Not Like "%[!A-Z]%"

i.e. designed to handle both query modes.

Jamie.

--

 

[Jamie Collins]

An input mask may be a simpler way of getting what you need. LLL as the
input mask would require three letters. Help has more information.
To usevalidation, if you set the Required property for the field to Yes and
Allow Zero Length to No, the rest of the rule:
Like "*[A-Z]*" And Not Like "*[0-9]*"
should work for the rest.

I think the problem is you didn't test the OP's rule

To test, while avoiding a CREATE TABLE scenario, I'll assume you have
a table called Test; if not substitute another table (having at least
one row) for Test in the following query:

SELECT 'A' + CHR$(140) + SPACE(1) + CHR$(59) + CHR$(45) + CHR$(41) AS
test_data, test_data Like "*[A-Z]*" And test_data Not Like "*[0-9]*"
AS result
FROM Test

result returns TRUE, meaning the illegal test data passes the rule,
therefore the rule does not implement the OP's clear spec, "only
alphabets" ;-)

Gunny's rule is good i.e. "can't contain anything but letters (A-Z)"
when implemented for both query modes.

Jamie.

--