Query Criteria - capturing data in a certain format

[dastr]

Hi all,

in a query, I would like to capture data that is in the following format:
123456 (6 digits) AND
A12345 (1 letter and 5 digits) AND
AB1234 (2 letts and 5 digits)

The letter could only be at the beginning of the abbreviation.

Do you have any suggestions?



Thanks in advance.

[June7]

So any of the 3 configurations are acceptable entries? Could try an expression in the ValidationRule property or a VBA procedure in the BeforeUpdate event of textbox to valdiate the entry.

x = field value

Possible ValidationRule:
IsNumeric(x) Or x Like "[A-Z]#####" Or x Like "[A-Z][A-Z]####"
Then ValidationText property: Enter value in format of 123456, A12345, AB1234

VBA something like:
If Not IsNumeric(x) And Not x Like "[A-Z]#####" And Not x Like "[A-Z][A-Z]####" Then
Cancel = True
MsgBox "Invalid entry."
End If