locked
Validation Rule RRS feed

  • Question

  • Hi,

    I have the following validation rule in a table on a field of text type.

    Not like "*[!a-z]*"

    This is used in several files on different computers.

    It works in some, in others it doesn't. For instance, I haven't been able to get it working in 2010. In 2007, only two computers, it works.

    I also see it automatically changing to Not Alike "*[!a-z]*"  in 2010, but it doesn't work. Thanks.


    hrubesh
    • Changed type Vanderghast Tuesday, July 26, 2011 6:15 PM As stated by the OP in a next post
    Friday, July 22, 2011 5:50 AM

Answers

  • Look at the Access Options panel, Object Designers pane; Query Design section; "SQL Server Compatible Syntax (ANSI 92)"; on your various machines/ Access db files.

    I heard recently from someone that "Alike" stands for ANSI-[compatible]-Like". Can anyone at Microsoft confirm whether this is correct?

    I suspect you are dealing with a variety of wildcard matching notations. Access has its own wildcard notation, whereas the rest of the SQL world has ANSI compatible wildcard notation. For compatibility, Access allows you to switch on compatibility with the rest of the SQL world (through ANSI standards). Switching this option ON necessarily switches OFF compatibility with traditional MS Access style wildcards. (Note however that even with ANSI syntax compatibility switched ON, you should still use Access style wildcards in VBA code with the "Like" comparison operator.)

    I suggest for you to use "SQL Server Compatible Syntax" in all your databases, and in all your Access files; and for you to switch to using the ANSI style wildcards in your databases. You will need to replace "*" with "%", etc. Search for "wildcard" in the Access help system and you should get some help... Although that help file is slightly misleading...

    "It is important to note that the ANSI SQL wildcards (%) and (_) are only available with the Microsoft Access database engine and the Access OLE DB Provider. They will be treated as literals if used through Access or DAO."

    Erm, no! Switch on ANSI compatibility, and you can use ANSI style wildcards with DAO.Recordset objects... Microsoft?

    Overall, you'll be better off in the long term with ANSI syntax. I suggest for you to standardise on that. Does this help?


    Matthew Slyman M.A. (Camb.)
    • Marked as answer by Vanderghast Tuesday, July 26, 2011 6:15 PM
    Friday, July 22, 2011 1:18 PM

All replies

  • Look at the Access Options panel, Object Designers pane; Query Design section; "SQL Server Compatible Syntax (ANSI 92)"; on your various machines/ Access db files.

    I heard recently from someone that "Alike" stands for ANSI-[compatible]-Like". Can anyone at Microsoft confirm whether this is correct?

    I suspect you are dealing with a variety of wildcard matching notations. Access has its own wildcard notation, whereas the rest of the SQL world has ANSI compatible wildcard notation. For compatibility, Access allows you to switch on compatibility with the rest of the SQL world (through ANSI standards). Switching this option ON necessarily switches OFF compatibility with traditional MS Access style wildcards. (Note however that even with ANSI syntax compatibility switched ON, you should still use Access style wildcards in VBA code with the "Like" comparison operator.)

    I suggest for you to use "SQL Server Compatible Syntax" in all your databases, and in all your Access files; and for you to switch to using the ANSI style wildcards in your databases. You will need to replace "*" with "%", etc. Search for "wildcard" in the Access help system and you should get some help... Although that help file is slightly misleading...

    "It is important to note that the ANSI SQL wildcards (%) and (_) are only available with the Microsoft Access database engine and the Access OLE DB Provider. They will be treated as literals if used through Access or DAO."

    Erm, no! Switch on ANSI compatibility, and you can use ANSI style wildcards with DAO.Recordset objects... Microsoft?

    Overall, you'll be better off in the long term with ANSI syntax. I suggest for you to standardise on that. Does this help?


    Matthew Slyman M.A. (Camb.)
    • Marked as answer by Vanderghast Tuesday, July 26, 2011 6:15 PM
    Friday, July 22, 2011 1:18 PM
  • That works and is an answer. Sorry I think I missed while using tab on the keyboard and change this to a discussion. Thanks, this works:

    Not Alike "%[!a-z]%"


    hrubesh
    Monday, July 25, 2011 5:58 AM