Compatible Syntax issues with ANSI92 in form Validation settings

[Phred]

Access 2007 front end, SQL Server 2008 R2 back end. All architecture completed. Starting design of first screen. This is my first combination Access to SQL Server development. Back end on Virtualized Server and Front end on Virtualized workstation. ODBC connection works. I am in a development environment. All architecture is complete and database generated to SQL Server.

Under Access Options, Object Designers, Query design, I checked SQL Server Compatible Syntax (ANSI92) and This database. Made sense to me since the back end is all SQL Server. Most of my queries I understand will run fine in Access others may need to be pass-through to SQL. I get this. Here is what's happening.

On the forms I disabled Autocorrect.

On a form I am attempting to enter an expression for Validation in the properties of a field:

Is Null OR Not Like “*[!0-9]*”



No matter what I do it is auto changed to

Is Null OR Not Alike “*[!0-9]*”

I realize this is due to changing to ANSI92. However the only solution I found said to go back into the Access Options and reverse my selection of ANSI92. I have a lot of validation rules to write. Is there any other solution? Have I made the correct decision to change to ANSI92? Do I need to move all my validation (and other expressions) to VB instead? Will they function correctly in VB? Do they get written in ANSI92 in VB? Does anyone know of a list of other conflicts and issues with ANSI92? Since the whole back end is SQL Server it doesn’t seem to make much sense to switch away from ANSI92.

Any insight is appreciated

Phred

[rpeare]

have you tried using the SQL version of a wildcard % instead of *

[rpeare]

err ALIKE is also the SQL version of LIKE so you should be fine on that syntax apart from the wildcard

[Phred]

Thanks rpeare. That helps. The piece of data I am entering is a PIN (Property Index Number). The actual PIN must be all digits 0-9 and be exactly 14 digits long. The number is never calculated so the SQL data type is a Varchar(14). The dashes are just an Access Mask and the mask is set to store the data without the symbols in the mask.

11-22-333-444-5555 passes the validation rule (Null or all characters 0-9). However I am having problems enforcing the exactly 14 digits. I have eliminated the Is Null OR Not Alike "%[!0-9]%" since it is functioning fine now and working on the 14 digits part.
Is Null OR Alike "##############" but it isn't working. If you delibertly enter 13 digits it catches the error. If you go back and add 1 more digit to = 14 it still errors out.

Is Null OR Alike "??????????????" doesn't seem to work either. Is there another ANSI92 problem here as well?

After getting the rule to enforce 14 digits, I need to combine both validation rules into one rule.

Thanks

Phred

[rpeare]

try

len([Fieldname]) = 14

[Phred]

Is Null Or Not Alike "%[!0-9]%" AND Len([KPIN]) = 14

Works great, thanks for your help.

Phred