NI numbers and validation

[MINA]

Hello

A new question on NI numbers and validation assuming below

National Insurance Number
1. Must be 9 characters.
2. First 2 characters must be alpha.
3. Next 6 characters must be numeric.
4. Final character can be A, B, C, D or space.
5. First character must not be D,F,I,Q,U or V
6. Second characters must not be D, F, I, O, Q, U or V.
7. First 2 characters must not be combinations of GB, NK, TN or ZZ (the term
combinations covers both GB and BG etc.)

and how do I write a validation rule on this?

Thanks

 

[peregenem]

A new question on NI numbers and validation assuming below

National Insurance Number
1. Must be 9 characters.
2. First 2 characters must be alpha.
3. Next 6 characters must be numeric.
4. Final character can be A, B, C, D or space.
5. First character must not be D,F,I,Q,U or V
6. Second characters must not be D, F, I, O, Q, U or V.
7. First 2 characters must not be combinations of GB, NK, TN or ZZ (the term
combinations covers both GB and BG etc.)

and how do I write a validation rule on this?

I'd suggest you write one Validation rule a.k.a. CHECK constraint per
business rule. It will make your code easier to test (more informative
to fail a specific rule than to fail a very general rule), debug and
maintain e.g. consider if one of the business rules change (say you
were later required to support temporary numbers - see below).

Note the following DDL uses Jet 4.0 wildcards (% = multiple characters,
_ = single character):

CREATE TABLE Test (
NINO CHAR(9) NOT NULL,
CONSTRAINT NINO_Must_be_9_characters
CHECK (LEN(NINO) = 9),
CONSTRAINT NINO_First_2_characters_must_be_alpha
CHECK (NINO LIKE '[A-Z][A-Z]%'),
CONSTRAINT NINO_characters_3_to_8_must_be_numeric
CHECK (NINO LIKE '__[0-9][0-9][0-9][0-9][0-9][0-9]_'),
CONSTRAINT NINO_Final_character_legal_values
CHECK (NINO LIKE '%[ ABCD]'),
CONSTRAINT NINO_First_character_illegal_values
CHECK (NINO NOT LIKE '_[DFIQUV]%'),
CONSTRAINT NINO_Second_character_illegal_values
CHECK (NINO NOT LIKE '_[DFIQUV]%'),
CONSTRAINT NINO_First_two_characters_illegal_combinations
CHECK (LEFT$(NINO, 2) NOT IN ('GB', 'BG', 'NK', 'KN', 'TN', 'NT',
'ZZ'))
);

Are these UK NI numbers? I thought 'TN' + DOD (DDMMYY) + (space) was a
legal value when you are advising the Revenue that you don't know the
actual value, requiring a separate CHECK to match NINO with DOB.
Perhaps your business rules explicitly require temporary NI numbers to
be excluded.

 

[MINA]

Many thanks for the reply.. I am quite new to Access can I write this in
Validation rule field in the table data sheet? I am not sure where to place
your code?

Thanks

Sorry for being so new to it all!

Mina

A new question on NI numbers and validation assuming below

National Insurance Number
1. Must be 9 characters.
2. First 2 characters must be alpha.
3. Next 6 characters must be numeric.
4. Final character can be A, B, C, D or space.
5. First character must not be D,F,I,Q,U or V
6. Second characters must not be D, F, I, O, Q, U or V.
7. First 2 characters must not be combinations of GB, NK, TN or ZZ (the term
combinations covers both GB and BG etc.)

and how do I write a validation rule on this?

I'd suggest you write one Validation rule a.k.a. CHECK constraint per
business rule. It will make your code easier to test (more informative
to fail a specific rule than to fail a very general rule), debug and
maintain e.g. consider if one of the business rules change (say you
were later required to support temporary numbers - see below).

Note the following DDL uses Jet 4.0 wildcards (% = multiple characters,
_ = single character):

CREATE TABLE Test (
NINO CHAR(9) NOT NULL,
CONSTRAINT NINO_Must_be_9_characters
CHECK (LEN(NINO) = 9),
CONSTRAINT NINO_First_2_characters_must_be_alpha
CHECK (NINO LIKE '[A-Z][A-Z]%'),
CONSTRAINT NINO_characters_3_to_8_must_be_numeric
CHECK (NINO LIKE '__[0-9][0-9][0-9][0-9][0-9][0-9]_'),
CONSTRAINT NINO_Final_character_legal_values
CHECK (NINO LIKE '%[ ABCD]'),
CONSTRAINT NINO_First_character_illegal_values
CHECK (NINO NOT LIKE '_[DFIQUV]%'),
CONSTRAINT NINO_Second_character_illegal_values
CHECK (NINO NOT LIKE '_[DFIQUV]%'),
CONSTRAINT NINO_First_two_characters_illegal_combinations
CHECK (LEFT$(NINO, 2) NOT IN ('GB', 'BG', 'NK', 'KN', 'TN', 'NT',
'ZZ'))
);

Are these UK NI numbers? I thought 'TN' + DOD (DDMMYY) + (space) was a
legal value when you are advising the Revenue that you don't know the
actual value, requiring a separate CHECK to match NINO with DOB.
Perhaps your business rules explicitly require temporary NI numbers to
be excluded.

 

[Dirk Goldgar]

A new question on NI numbers and validation assuming below

National Insurance Number
1. Must be 9 characters.
2. First 2 characters must be alpha.
3. Next 6 characters must be numeric.
4. Final character can be A, B, C, D or space.
5. First character must not be D,F,I,Q,U or V
6. Second characters must not be D, F, I, O, Q, U or V.
7. First 2 characters must not be combinations of GB, NK, TN or ZZ
(the term combinations covers both GB and BG etc.)

and how do I write a validation rule on this?

I'd suggest you write one Validation rule a.k.a. CHECK constraint per
business rule. It will make your code easier to test (more informative
to fail a specific rule than to fail a very general rule), debug and
maintain e.g. consider if one of the business rules change (say you
were later required to support temporary numbers - see below).

Note the following DDL uses Jet 4.0 wildcards (% = multiple
characters, _ = single character):

CREATE TABLE Test (
NINO CHAR(9) NOT NULL,
CONSTRAINT NINO_Must_be_9_characters
CHECK (LEN(NINO) = 9),
CONSTRAINT NINO_First_2_characters_must_be_alpha
CHECK (NINO LIKE '[A-Z][A-Z]%'),
CONSTRAINT NINO_characters_3_to_8_must_be_numeric
CHECK (NINO LIKE '__[0-9][0-9][0-9][0-9][0-9][0-9]_'),
CONSTRAINT NINO_Final_character_legal_values
CHECK (NINO LIKE '%[ ABCD]'),
CONSTRAINT NINO_First_character_illegal_values
CHECK (NINO NOT LIKE '_[DFIQUV]%'),
CONSTRAINT NINO_Second_character_illegal_values
CHECK (NINO NOT LIKE '_[DFIQUV]%'),
CONSTRAINT NINO_First_two_characters_illegal_combinations
CHECK (LEFT$(NINO, 2) NOT IN ('GB', 'BG', 'NK', 'KN', 'TN', 'NT',
'ZZ'))
);

Are these UK NI numbers? I thought 'TN' + DOD (DDMMYY) + (space) was a
legal value when you are advising the Revenue that you don't know the
actual value, requiring a separate CHECK to match NINO with DOB.
Perhaps your business rules explicitly require temporary NI numbers to
be excluded.

MINA -

Be aware that you'll need to use ADO to execute the query that pergenem
proposes, or else set your database option to use ANSI '92 ("SQL Server
compatible") SQL.

If you don't want to do that, you can create a single validation rule,
in table design view, that combines all the constraints into a single
logical expression with clauses connected by "And". You'll also need to
translate the wildcard characters '%' and '_' to '*' and '%'. It might
look like this:

LEN([NINO]) = 9 And [NINO] LIKE '[A-Z][A-Z]*' And [NINO] LIKE
'??[0-9][0-9][0-9][0-9][0-9][0-9]?' And [NINO] LIKE '*[ ABCD]' And
[NINO] NOT LIKE '?[DFIQUV]*' And [NINO] NOT LIKE '?[DFIQUV]*' and
LEFT$([NINO], 2) NOT IN ('GB', 'BG', 'NK', 'KN', 'TN', 'NT', 'ZZ')

I'm not sure how easy it will be to get a trailing space in a NINO field
if you enter it via a text box, since I think normally a text box
truncates trailing spaces. This may need a workaround.

 

[MINA]

Hello

Mnay thanks for this.. I have pasted this into Validation rule filed in the
table but it says that it has illegal characters etc.. anyway i try a bit
more..

Mina

A new question on NI numbers and validation assuming below

National Insurance Number
1. Must be 9 characters.
2. First 2 characters must be alpha.
3. Next 6 characters must be numeric.
4. Final character can be A, B, C, D or space.
5. First character must not be D,F,I,Q,U or V
6. Second characters must not be D, F, I, O, Q, U or V.
7. First 2 characters must not be combinations of GB, NK, TN or ZZ
(the term combinations covers both GB and BG etc.)

and how do I write a validation rule on this?

I'd suggest you write one Validation rule a.k.a. CHECK constraint per
business rule. It will make your code easier to test (more informative
to fail a specific rule than to fail a very general rule), debug and
maintain e.g. consider if one of the business rules change (say you
were later required to support temporary numbers - see below).

Note the following DDL uses Jet 4.0 wildcards (% = multiple
characters, _ = single character):

CREATE TABLE Test (
NINO CHAR(9) NOT NULL,
CONSTRAINT NINO_Must_be_9_characters
CHECK (LEN(NINO) = 9),
CONSTRAINT NINO_First_2_characters_must_be_alpha
CHECK (NINO LIKE '[A-Z][A-Z]%'),
CONSTRAINT NINO_characters_3_to_8_must_be_numeric
CHECK (NINO LIKE '__[0-9][0-9][0-9][0-9][0-9][0-9]_'),
CONSTRAINT NINO_Final_character_legal_values
CHECK (NINO LIKE '%[ ABCD]'),
CONSTRAINT NINO_First_character_illegal_values
CHECK (NINO NOT LIKE '_[DFIQUV]%'),
CONSTRAINT NINO_Second_character_illegal_values
CHECK (NINO NOT LIKE '_[DFIQUV]%'),
CONSTRAINT NINO_First_two_characters_illegal_combinations
CHECK (LEFT$(NINO, 2) NOT IN ('GB', 'BG', 'NK', 'KN', 'TN', 'NT',
'ZZ'))
);

Are these UK NI numbers? I thought 'TN' + DOD (DDMMYY) + (space) was a
legal value when you are advising the Revenue that you don't know the
actual value, requiring a separate CHECK to match NINO with DOB.
Perhaps your business rules explicitly require temporary NI numbers to
be excluded.

MINA -

Be aware that you'll need to use ADO to execute the query that pergenem
proposes, or else set your database option to use ANSI '92 ("SQL Server
compatible") SQL.

If you don't want to do that, you can create a single validation rule,
in table design view, that combines all the constraints into a single
logical expression with clauses connected by "And". You'll also need to
translate the wildcard characters '%' and '_' to '*' and '%'. It might
look like this:

LEN([NINO]) = 9 And [NINO] LIKE '[A-Z][A-Z]*' And [NINO] LIKE
'??[0-9][0-9][0-9][0-9][0-9][0-9]?' And [NINO] LIKE '*[ ABCD]' And
[NINO] NOT LIKE '?[DFIQUV]*' And [NINO] NOT LIKE '?[DFIQUV]*' and
LEFT$([NINO], 2) NOT IN ('GB', 'BG', 'NK', 'KN', 'TN', 'NT', 'ZZ')

I'm not sure how easy it will be to get a trailing space in a NINO field
if you enter it via a text box, since I think normally a text box
truncates trailing spaces. This may need a workaround.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hello

Mnay thanks for this.. I have pasted this into Validation rule filed
in the table but it says that it has illegal characters etc.. anyway
i try a bit more..

I tested it in a very perfunctory way and it seemed to work, but it
could well be that I made a mistake somewhere. But when you say "it
says that it has illegal characters", are you talking about an error
message you get when you paste it into the Validation Rule property, or
are you just saing that it rejects some valid NI numbers? If the
former, maybe you just didn't jpin up the lines so that the rule was all
on a single line.

 

[peregenem]

CONSTRAINT NINO_First_character_illegal_values
CHECK (NINO NOT LIKE '_[DFIQUV]%')

Typo. Should be

CONSTRAINT NINO_First_character_illegal_values
CHECK (NINO NOT LIKE '[DFIQUV]%')

 

[MINA]

Hello

Thanks for the reply. Yes I have pasted the whole thing into the table
validation rule properties.. and does not work due to illegal characters.. So
I will give up on it.. In away I could not have a chance to see if the rule
was valideting but looks OK to me from what you have got..

I know Excel and I could do this with If nested statement but can not seem
to understand access's ways so far..

Thanks again.

Mina

 

[Dirk Goldgar]

Hello

Thanks for the reply. Yes I have pasted the whole thing into the table
validation rule properties.. and does not work due to illegal
characters.. So I will give up on it.. In away I could not have a
chance to see if the rule was valideting but looks OK to me from what
you have got..

I don't understand exactly what the problem is. As I said, I tested it
myself and had no syntactical problems, though as I said I didn't test
it thoroughly to verify that it accepted valid NI numbers and rejected
invalid ones.

I know Excel and I could do this with If nested statement but can not
seem to understand access's ways so far..

It's better to do this kind of data validation in a declarative way
where possible, and I believe it should be possible -- and perigenem's
set of CHECK constraints also works fine, once you know how to put them
in place. But if you can't get this to work, you can program it into
the BeforeUpdate event of a text box on a form.