Input Mask vs Validation Rule

[adulted]

As with the previous example my situation is simalar. I only want a total of 7 entries, although it must be 2 Uppercasae Letters, followed by 5 Numbers.

I used the formula Len([isbn])=7 which does limit to 7 total characters, and tried adding > in Format, but I can't seem to get my validation text to display when wrongfully entered.

In short, for the fieldname ISBN I need to have only as examples the following to be entered.

ER45187, TC91672, GP82917 etc.. If not, then display my validation text error message:

ISBN must be 2 letters followed by 5 numbers.

Thanks

 

[Norie]

Why not set the length of the ISBN field to 7?

And use this input mask:

>LL99999

This won't display a validation message but will restrict entries to two characters followed by 5 digits.

 

[adulted]

Thanks Norie,

Yes, that does work, however, I need to have the error message show if this is not met.

Format: >
Validation Rule: Like "LL9999"
Validation Text: ISBN must be two letters, followed by 5 numbers

This doesn't work.

Thanks

 

[Norie]

I thought you might need it.

The only way I could think of would be to write a user defined function that does the validation, but I'm not sure if you can use them in validation rules.

BTW is this for a table or a form? If it's for a form code could be written that checks the entry and displays a message box.

 

[adulted]

Hi Norie,

Yes, it's a table. It came about from showing how to assign a validation rule & text when used as a primary key, rather than the usual AutoNumber for the key. In this Example:

DoctorID
FieldSize 4
Format >
Validation Rule Like "D###"
Validation Text The DoctorID must begin with the letter D followed by 3 digits

Now I'm trying to figure a way for a books table using BookID with the data type of AutoNumber and when data entered for ISBN, the same likewise example forcing the data entry person to have to enter it as example ER54789, in saying Two letters followed by 5 numbers, if not an error message in the Validation text reading Two Letters, followed by 5 Numbers.

Any two letters, followed by any 5 Numbers.

Thanks

 

[buckeye_td]

You can put this in the Validation Rule:

(Left([isbn],2) Between "AA" And "ZZ") And (Right([ISBN],5) Between "00000" And "99999")

and then put something like this in the Validation Text:

Entry must be 2 Alpha Characters followed by 5 Numeric

TD

 

[adulted]

Thanks buckeye_td


Appreciate all the fine answers.

Paul