Validation rule, setting two Characters and six numbers

[Guest]

Can any body tell me how to set up a validation rule that allows me to only
enter two characters, six numbers and one character.

 

[Dirk Goldgar]

Can any body tell me how to set up a validation rule that allows me
to only enter two characters, six numbers and one character.

If by "character" you mean any character, not just a letter, then you
might use

Like "??######?"

If by "character" you mean a letter (A to Z), you could use this:

Like "[A-Z][A-Z]######[A-Z]"

 

[Jamie Collins]

Can any body tell me how to set up a validation rule that allows me
to only enter two characters, six numbers and one character.

If by "character" you mean a letter (A to Z), you could use this:

Like "[A-Z][A-Z]######[A-Z]"

But it wouldn't prevent something like this:

CurrentProject.Connection.Execute "INSERT INTO Test VALUES
('AA######A');"

Better to use an 'ANSI-neutral' pattern e.g.

Like "[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]"

FWIW I suspect there may be more validation required than suggested
(and the above pattern may not be appropriate to support older TN
numbers). See:

http://www.govtalk.gov.uk/gdsc/html/frames/NationalInsuranceNumber-2-1-Release.htm

Jamie.

--

 

[Dirk Goldgar]

Can any body tell me how to set up a validation rule that allows me
to only enter two characters, six numbers and one character.

If by "character" you mean a letter (A to Z), you could use this:

Like "[A-Z][A-Z]######[A-Z]"

But it wouldn't prevent something like this:

CurrentProject.Connection.Execute "INSERT INTO Test VALUES
('AA######A');"

Better to use an 'ANSI-neutral' pattern e.g.

Like "[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]"

True. Depending on the circumstances, it may be necessary or advisable
to do that.

FWIW I suspect there may be more validation required than suggested
(and the above pattern may not be appropriate to support older TN
numbers). See:

http://www.govtalk.gov.uk/gdsc/html/frames/NationalInsuranceNumber-2-1-Release.htm

You may be right that this is a NINO validation being sought. We had a
thread about validating these numbers a while back; a Google Groups
search might find it.