I have to set a validation rule with customised error message for a national
insurance number. This has two letters, six numbers in groups of 2 and 1
letter.
I have tried
Like "AA" " " "11" " " "11"" " "11" " " "A" where A represents a letter, 1 a
number and " " a space. Whatever I key into the table generates the same
error message - whether it is right or wrong.
I can do it with an input mask LL 00 00 00 L but only get a beep, not a
customised error messsage. I have tried setting the input mask with a
validation error message to no avail.
I am using Access 2003 Database.
KG

Hi KG

The pattern matching string for Like is very different from that for an
input mask. You can find out all about it if you search for Like in the
online help.

To get you going, the following expression should work:

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

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


"KG" <(E-Mail Removed)> wrote in message
news:B86DBE70-A271-4BBC-8142-(E-Mail Removed)...
>I have to set a validation rule with customised error message for a
>national
> insurance number. This has two letters, six numbers in groups of 2 and 1
> letter.
> I have tried
> Like "AA" " " "11" " " "11"" " "11" " " "A" where A represents a letter, 1
> a
> number and " " a space. Whatever I key into the table generates the same
> error message - whether it is right or wrong.
> I can do it with an input mask LL 00 00 00 L but only get a beep, not a
> customised error messsage. I have tried setting the input mask with a
> validation error message to no avail.
> I am using Access 2003 Database.
> KG

Hi Graham,
Thank you so much - at about 10.30 last night I finally realised how to do
it but the help page I used suggested a single quote mark after the Like and
at the end of the string. I also discovered that spaces can be inserted by
using [ ] and symbols too.

Once again - thanks. Kate - KG

"Graham Mandeno" wrote:

> Hi KG
>
> The pattern matching string for Like is very different from that for an
> input mask. You can find out all about it if you search for Like in the
> online help.
>
> To get you going, the following expression should work:
>
> Like "[A-Z][A-Z] ## ## ## [A-Z]"
>
> --
> Good Luck!
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
>
> "KG" <(E-Mail Removed)> wrote in message
> news:B86DBE70-A271-4BBC-8142-(E-Mail Removed)...
> >I have to set a validation rule with customised error message for a
> >national
> > insurance number. This has two letters, six numbers in groups of 2 and 1
> > letter.
> > I have tried
> > Like "AA" " " "11" " " "11"" " "11" " " "A" where A represents a letter, 1
> > a
> > number and " " a space. Whatever I key into the table generates the same
> > error message - whether it is right or wrong.
> > I can do it with an input mask LL 00 00 00 L but only get a beep, not a
> > customised error messsage. I have tried setting the input mask with a
> > validation error message to no avail.
> > I am using Access 2003 Database.
> > KG
>
>
>