Validation Rules

[Guest]

hi, in my database i have a field that i want to place a validation rule on,
it is for Order Codes and has only two types of values that have the format:
"LetterLetterNumberNumberNumber" e.g. AA123 or
"LetterLetterNumberNumberNumberNumber" e.g. AA1234
the letters can be any letter and the number any number but just the format
needs to be as above.
what kind of validation rule can i use for this field?

Thanks

 

[kingston via AccessMonster.com]

Did you try using an Input Mask?
LL0009

hi, in my database i have a field that i want to place a validation rule on,
it is for Order Codes and has only two types of values that have the format:
"LetterLetterNumberNumberNumber" e.g. AA123 or
"LetterLetterNumberNumberNumberNumber" e.g. AA1234
the letters can be any letter and the number any number but just the format
needs to be as above.
what kind of validation rule can i use for this field?

Thanks

 

[Guest]

yes i have tried this before i posted the question...it seems you can specify
a validation for letters and numbers but on;y when the first letter is
specified and not a general vlaidation.

thank you for you kind response

 

[Guest]

Here is a start. The first allows only UCase and the second allows both case.
(Asc(Left([yy],1)) Between 65 And 90) and (Asc(Right(Left([yy],2),1))
Between 65 And 90)

(Asc(Left([yy],1)) Between 65 And 90 Or Asc(Left([yy],1)) Between 97 And
122) and (Asc(Right(Left([yy],2),1)) Between 65 And 90 Or
Asc(Right(Left([yy],2),1)) Between 97 And 122)

You can continue to build it. ASCII for zero is 48 and for nine is 57.

 

[Jamie Collins]

LIKE '[A-Z][A-Z][0-9][0-9][0-9]' OR LIKE
'[A-Z][A-Z][0-9][0-9][0-9][0-9]'

You said "any number" but you should consider whether leading zeros are
allowed e.g. are 'AA001' and 'AA0001' distinct values?

Jamie.

--

 

[Guest]

Thanks!!! it works...and thank your for your suggestion on the leading
zeros...for these field any AA000 is acceptable for the record and the
validation rule allows it...so thank you again.

LIKE '[A-Z][A-Z][0-9][0-9][0-9]' OR LIKE
'[A-Z][A-Z][0-9][0-9][0-9][0-9]'

You said "any number" but you should consider whether leading zeros are
allowed e.g. are 'AA001' and 'AA0001' distinct values?

Jamie.