Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Validation Rule - Access 97 VS 2002

Discussion in 'Information Technology' started by Pradeep, Jul 28, 2006.

  1. Pradeep

    Pradeep
    Expand Collapse
    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
     
  2. Loading...


  3. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Pradeep wrote:
    > 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.

    --
     
  4. Pradeep

    Pradeep
    Expand Collapse
    Guest

    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" wrote:

    >
    > Pradeep wrote:
    > > 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.
    >
    > --
    >
    >
     
  5. Pradeep

    Pradeep
    Expand Collapse
    Guest

    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" wrote:

    >
    > Pradeep wrote:
    > > 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.
    >
    > --
    >
    >
     
  6. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Pradeep wrote:
    > 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)


    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.

    --
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Jamie Collins wrote:
    > Bottom line: your validation rule offers no protection against a user
    > connected using ADO


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

    Jamie.

    --
     
  8. Pradeep

    Pradeep
    Expand Collapse
    Guest

    Got It.

    Thanks Jamie, You were very helpful.

    Thanks,

    Pradeep

    "Jamie Collins" wrote:

    >
    > Jamie Collins wrote:
    > > Bottom line: your validation rule offers no protection against a user
    > > connected using ADO

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

Share This Page