Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

NI numbers and validation

Discussion in 'Information Technology' started by MINA, Oct 29, 2005.

  1. MINA

    MINA
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    MINA wrote:
    > 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.
     
  4. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    <peregenem@jetemail.net> wrote in message
    news:1130517111.154767.259790@g44g2000cwa.googlegroups.com
    > MINA wrote:
    >> 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)
     
  5. MINA

    MINA
    Expand Collapse
    Guest

    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



    "peregenem@jetemail.net" wrote:

    >
    > MINA wrote:
    > > 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.
    >
    >
     
  6. MINA

    MINA
    Expand Collapse
    Guest

    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

    "Dirk Goldgar" wrote:

    > <peregenem@jetemail.net> wrote in message
    > news:1130517111.154767.259790@g44g2000cwa.googlegroups.com
    > > MINA wrote:
    > >> 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)
    >
    >
    >
    >
     
  7. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "MINA" <MINA@discussions.microsoft.com> wrote in message
    news:A30E6637-2D9D-40B8-B8EF-117BED57E803@microsoft.com
    > 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.

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

    (please reply to the newsgroup)
     
  8. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    peregenem@jetemail.net wrote:
    > 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]%')
     
  9. MINA

    MINA
    Expand Collapse
    Guest

    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" wrote:

    > "MINA" <MINA@discussions.microsoft.com> wrote in message
    > news:A30E6637-2D9D-40B8-B8EF-117BED57E803@microsoft.com
    > > 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.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     
  10. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "MINA" <MINA@discussions.microsoft.com> wrote in message
    news:0ABA570A-D2E1-44C6-9C7B-17C1B574B0AC@microsoft.com
    > 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.

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

    (please reply to the newsgroup)
     

Share This Page