Validation Rule - Access 97 VS 2002

[Pradeep]

Hello,

I have a database in Access 97 which I have recently coverted to Access 2002
file format. I have observed a strange behaviour with respect to a validation
rule for a field in a table which is as follows:

Not Like "*[#]*" And Not Like "*[*]*" And Not Like "*[?]*" And Not Like
"*[!]*"

and Validation text "Not Allowed"

The idea here was to restrict the entry of strings which have characters
#,*,? and ! in them. In the help provided in Access 97 itself it says that
for restricting ! we do not need sqaure brackets on either sides. However
this rule still works fine and is restricting strings with any of the above
characters.

Now because of using the square brackets in Access 2002, the field is not
allowing the entry of text at all even though the strings do not contain the
four characters (# * ? !). I removed the square brackets and now it is
working as expected restricting strings with those characters. Now the
validation rule is:

Not Like "*[#]*" And Not Like "*[*]*" And Not Like "*[?]*" And Not Like "*!*"

Why this difference in terms of validating the characters and if use *[!]*,
why is it restricting the entry of text at all.

Thanks,

Pradeep Varma

 

[Pradeep]

Hello Jamie,

I aprpeciate your response in suggesting a better way.

I have the rule working properly but was wondering as to why it was behaving
differently in both the versions.

Thanks,

Pradeep Varma

The idea here was to restrict the entry of strings which have characters
#,*,? and ! in them.

Suggestion:

text_col NOT LIKE '*[#*?!]*'

Further suggestion: do not assume * is the wild card character; in
'ANSI' mode the relevant wildcard character is %:

text_col NOT LIKE '*[#*?!]*' AND text_col NOT LIKE '%[#*?!]%'

Jamie.

 

[Pradeep]

Hello Jamie,

Just wanted a confirmation on your statement regarding not assuming * as the
wildcard character mode in ANSI.

For the same reason I did not check the option of ANSI 92 in the
Tools-->Options dialog box.

In that case * should be working perfectly, right? (I mean * would be the
wildcard character if I do not check that option)

Thanks,

Pradeep

The idea here was to restrict the entry of strings which have characters
#,*,? and ! in them.

Suggestion:

text_col NOT LIKE '*[#*?!]*'

Further suggestion: do not assume * is the wild card character; in
'ANSI' mode the relevant wildcard character is %:

text_col NOT LIKE '*[#*?!]*' AND text_col NOT LIKE '%[#*?!]%'

Jamie.

 

[Pradeep]

Got It.

Thanks Jamie, You were very helpful.

Thanks,

Pradeep