Validation Rule - Access 97 VS 2002

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

Guest

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 said:
The idea here was to restrict the entry of strings which have characters
#,*,? and ! in them.
Click to expand...

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.

--
 
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

Jamie Collins said:
The idea here was to restrict the entry of strings which have characters
#,*,? and ! in them.
Click to expand...

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.
Click to expand...
 
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



Jamie Collins said:
The idea here was to restrict the entry of strings which have characters
#,*,? and ! in them.
Click to expand...

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.
Click to expand...
 
Pradeep said:
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)
Click to expand...

Not correct. If a user was connected to your database via ADO (and if
they have a valid username and password, what's to stop them?) then the
equivalent wildcard character would be %, regardless of whether the
database is set to 'ANSI' mode or otherwise. ADO would 'see' the *
character as a literal.

Bottom line: your validation rule offers no protection against a user
connected using ADO; they would be able to insert any value into the
column. Using both flavours of wildcard prevents users from
circumventing your constraints and (accidentally or otherwise)
destroying your data integrity by simply switching between ADO and DAO.

Jamie.

--
 
Jamie said:
Bottom line: your validation rule offers no protection against a user
connected using ADO
Click to expand...

This is not limited to ADO; the same applies to any OLE DB connection.

Jamie.

--
 
Back
Top