Hello All

I have a field 'ni_number' in a table which must always be 2 alphas then 6
digits then 1 alpha, and I have the validation for this in place. In fact,
however, the first 2 alphas must be from a defined set of (I think it's
around 150) allowable combinations, and the last alpha must be one of 6.
What's the best way of adding this validation? Obviously I could just have a
very long rule with 150 "Or"s, but that seems clumsy. I can think of
creating a new table [ni_valid_data], with 3 fields (say 'ni_serial' (key),
'prefix' and 'suffix') corresponding to the 2 valid datasets, and then
having a validation rule along the lines of

DCount([ni_valid_data]![ni_serial], [ni_valid_data], [prefix] =
Left(ni_number,2)) =1

.... but I don't think I could use this at table level, and in any case I
suspect there is a better way!!

Hope someone can help.
Many thanks
Leslie Isaacs

Sounds like your field is meant to hold three separate facts, which is a
violation of referential database principles. Recommend you create three
text fields of lenght 2, 6 and 1 respectively. Create a table of allowable
2-letter combos and use it as the row source for a combobox picklist to
populate the 2-char field. Make the format of the 6-char field numeric and
allow user input. For the 1-char field, replicate the combobox process, with
rowsource being a table of allowable single letters. That way, if the
allowable alphas change, you can just change the tables, not your validation
code.
For displaying in forms and reports, you can concatenate the three fields in
a query.
-TedMi

"PayeDoc" <(E-Mail Removed)> wrote in message
news:eIv5RI$(E-Mail Removed)...
> Hello All
>
> I have a field 'ni_number' in a table which must always be 2 alphas then 6
> digits then 1 alpha, and I have the validation for this in place. In fact,
> however, the first 2 alphas must be from a defined set of (I think it's
> around 150) allowable combinations, and the last alpha must be one of 6.
> What's the best way of adding this validation? Obviously I could just have
> a
> very long rule with 150 "Or"s, but that seems clumsy. I can think of
> creating a new table [ni_valid_data], with 3 fields (say 'ni_serial'
> (key),
> 'prefix' and 'suffix') corresponding to the 2 valid datasets, and then
> having a validation rule along the lines of
>
> DCount([ni_valid_data]![ni_serial], [ni_valid_data], [prefix] =
> Left(ni_number,2)) =1
>
> ... but I don't think I could use this at table level, and in any case I
> suspect there is a better way!!
>
> Hope someone can help.
> Many thanks
> Leslie Isaacs
>
>
>

Hello Ted

Many thanks for your reply.

I fully understand your comments about splitting the ni_number field, and
that from my first description this field does seem to be 3 wrapped in 1:
but in fact the 'ni_number' really is a single entity (it's an employee's
National Insurance number, allocated by HM Government!!), and it would be
quite cumbersome for users having to enter these values in 3 parts. I
realise that I could make the inputting of the 3 parts reasonably 'seamless,
but it would still mean more complexity on the form - and in fact there are
3 forms where these values are currently added, and umpteen reports where I
would need to concatenate the parts ... so I'm pretty keen if possible to
find a way of applying the validation at table level!!

Can you see how I could do this?

Thanks again
Les


"TedMi" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sounds like your field is meant to hold three separate facts, which is a
> violation of referential database principles. Recommend you create three
> text fields of lenght 2, 6 and 1 respectively. Create a table of allowable
> 2-letter combos and use it as the row source for a combobox picklist to
> populate the 2-char field. Make the format of the 6-char field numeric and
> allow user input. For the 1-char field, replicate the combobox process,
with
> rowsource being a table of allowable single letters. That way, if the
> allowable alphas change, you can just change the tables, not your
validation
> code.
> For displaying in forms and reports, you can concatenate the three fields
in
> a query.
> -TedMi
>
> "PayeDoc" <(E-Mail Removed)> wrote in message
> news:eIv5RI$(E-Mail Removed)...
> > Hello All
> >
> > I have a field 'ni_number' in a table which must always be 2 alphas then
6
> > digits then 1 alpha, and I have the validation for this in place. In
fact,
> > however, the first 2 alphas must be from a defined set of (I think it's
> > around 150) allowable combinations, and the last alpha must be one of 6.
> > What's the best way of adding this validation? Obviously I could just
have
> > a
> > very long rule with 150 "Or"s, but that seems clumsy. I can think of
> > creating a new table [ni_valid_data], with 3 fields (say 'ni_serial'
> > (key),
> > 'prefix' and 'suffix') corresponding to the 2 valid datasets, and then
> > having a validation rule along the lines of
> >
> > DCount([ni_valid_data]![ni_serial], [ni_valid_data], [prefix] =
> > Left(ni_number,2)) =1
> >
> > ... but I don't think I could use this at table level, and in any case I
> > suspect there is a better way!!
> >
> > Hope someone can help.
> > Many thanks
> > Leslie Isaacs
> >
> >
> >
>
>

Leslie

I agree with TedMi that the different components beg for storage in
different fields.

However, if HM Government won't change (HAH!), then one approach to
validating thi would be to build a routine that runs as the user leaves the
field. That validation routine could use the Left(), Mid() and Right()
functions to grab off the pieces, and the In() function to test for whether
the piece held a valid value.

That said, why are you forcing users to enter a 'code number'? Wouldn't it
be easier for them to identify a person by name and confirm the ni_number
than have to enter the number and confirm the name?

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"PayeDoc" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello Ted
>
> Many thanks for your reply.
>
> I fully understand your comments about splitting the ni_number field, and
> that from my first description this field does seem to be 3 wrapped in 1:
> but in fact the 'ni_number' really is a single entity (it's an employee's
> National Insurance number, allocated by HM Government!!), and it would be
> quite cumbersome for users having to enter these values in 3 parts. I
> realise that I could make the inputting of the 3 parts reasonably
> 'seamless,
> but it would still mean more complexity on the form - and in fact there
> are
> 3 forms where these values are currently added, and umpteen reports where
> I
> would need to concatenate the parts ... so I'm pretty keen if possible to
> find a way of applying the validation at table level!!
>
> Can you see how I could do this?
>
> Thanks again
> Les
>
>
> "TedMi" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Sounds like your field is meant to hold three separate facts, which is a
>> violation of referential database principles. Recommend you create three
>> text fields of lenght 2, 6 and 1 respectively. Create a table of
>> allowable
>> 2-letter combos and use it as the row source for a combobox picklist to
>> populate the 2-char field. Make the format of the 6-char field numeric
>> and
>> allow user input. For the 1-char field, replicate the combobox process,
> with
>> rowsource being a table of allowable single letters. That way, if the
>> allowable alphas change, you can just change the tables, not your
> validation
>> code.
>> For displaying in forms and reports, you can concatenate the three fields
> in
>> a query.
>> -TedMi
>>
>> "PayeDoc" <(E-Mail Removed)> wrote in message
>> news:eIv5RI$(E-Mail Removed)...
>> > Hello All
>> >
>> > I have a field 'ni_number' in a table which must always be 2 alphas
>> > then
> 6
>> > digits then 1 alpha, and I have the validation for this in place. In
> fact,
>> > however, the first 2 alphas must be from a defined set of (I think it's
>> > around 150) allowable combinations, and the last alpha must be one of
>> > 6.
>> > What's the best way of adding this validation? Obviously I could just
> have
>> > a
>> > very long rule with 150 "Or"s, but that seems clumsy. I can think of
>> > creating a new table [ni_valid_data], with 3 fields (say 'ni_serial'
>> > (key),
>> > 'prefix' and 'suffix') corresponding to the 2 valid datasets, and then
>> > having a validation rule along the lines of
>> >
>> > DCount([ni_valid_data]![ni_serial], [ni_valid_data], [prefix] =
>> > Left(ni_number,2)) =1
>> >
>> > ... but I don't think I could use this at table level, and in any case
>> > I
>> > suspect there is a better way!!
>> >
>> > Hope someone can help.
>> > Many thanks
>> > Leslie Isaacs
>> >
>> >
>> >
>>
>>
>
>

Jeff

Many thanks for your response.

I'm not sure whether this is relevant, but the only sense in which the NI
number has "different components" is from the access-validation perspective.
Neither anyone in HM Government, nor any of the people who have an NI number
(and that's most of the UK population over 16 years old), would think of
anything other than the complete, 9-character 'number'.

Regarding the entry of this number, it is not a case of identifying the
person and then confirming the NI number (or vice versa): it's a case of
entering new records, and for each new record a name, and an NI number, and
lots of other attributes, have to be entered. Once the NI number has been
entered it is rarely - if ever - accessed again (but it is output in very
many reports).

So I'm back to wanting to validate the first 2, and last, characters,
which I can see how to do with Left, Mid etc. functions on the form (in fact
I would have to do it on 3 forms), but I can't see how to avoid a very long
validation expression - given that there are ~150 valid first-two-character
combos. It seemed to me that it would be far better to validate against a
table of valid values (rather than a very long string), and also to do it at
table level rather than form level.

Hope that explains things better - and that I'm not missing the point!

Thanks again
Les


"Jeff Boyce" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Leslie
>
> I agree with TedMi that the different components beg for storage in
> different fields.
>
> However, if HM Government won't change (HAH!), then one approach to
> validating thi would be to build a routine that runs as the user leaves
the
> field. That validation routine could use the Left(), Mid() and Right()
> functions to grab off the pieces, and the In() function to test for
whether
> the piece held a valid value.
>
> That said, why are you forcing users to enter a 'code number'? Wouldn't
it
> be easier for them to identify a person by name and confirm the
ni_number
> than have to enter the number and confirm the name?
>
> JOPO (just one person's opinion)
>
> Regards
>
> Jeff Boyce
> Microsoft Access MVP
>
> --
> Disclaimer: This author may have received products and services
mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
>
> Any code or psuedocode included in this post is offered "as is", with no
> guarantee as to suitability.
>
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
>
> "PayeDoc" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hello Ted
>>
>> Many thanks for your reply.
>>
>> I fully understand your comments about splitting the ni_number field,
and
>> that from my first description this field does seem to be 3 wrapped in
1:
>> but in fact the 'ni_number' really is a single entity (it's an
employee's
>> National Insurance number, allocated by HM Government!!), and it would
be
>> quite cumbersome for users having to enter these values in 3 parts. I
>> realise that I could make the inputting of the 3 parts reasonably
>> 'seamless,
>> but it would still mean more complexity on the form - and in fact there
>> are
>> 3 forms where these values are currently added, and umpteen reports
where
>> I
>> would need to concatenate the parts ... so I'm pretty keen if possible
to
>> find a way of applying the validation at table level!!
>>
>> Can you see how I could do this?
>>
>> Thanks again
>> Les
>>
>>
>> "TedMi" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Sounds like your field is meant to hold three separate facts, which is
a
>>> violation of referential database principles. Recommend you create
three
>>> text fields of lenght 2, 6 and 1 respectively. Create a table of
>>> allowable
>>> 2-letter combos and use it as the row source for a combobox picklist
to
>>> populate the 2-char field. Make the format of the 6-char field numeric
>>> and
>>> allow user input. For the 1-char field, replicate the combobox
process,
>> with
>>> rowsource being a table of allowable single letters. That way, if the
>>> allowable alphas change, you can just change the tables, not your
>> validation
>>> code.
>>> For displaying in forms and reports, you can concatenate the three
fields
>> in
>>> a query.
>>> -TedMi
>>>
>>> "PayeDoc" <(E-Mail Removed)> wrote in message
>>> news:eIv5RI$(E-Mail Removed)...
>>> > Hello All
>>> >
>>> > I have a field 'ni_number' in a table which must always be 2 alphas
>>> > then
>> 6
>>> > digits then 1 alpha, and I have the validation for this in place. In
>> fact,
>>> > however, the first 2 alphas must be from a defined set of (I think
it's
>>> > around 150) allowable combinations, and the last alpha must be one
of
>>> > 6.
>>> > What's the best way of adding this validation? Obviously I could
just
>> have
>>> > a
>>> > very long rule with 150 "Or"s, but that seems clumsy. I can think of
>>> > creating a new table [ni_valid_data], with 3 fields (say 'ni_serial'
>>> > (key),
>>> > 'prefix' and 'suffix') corresponding to the 2 valid datasets, and
then
>>> > having a validation rule along the lines of
>>> >
>>> > DCount([ni_valid_data]![ni_serial], [ni_valid_data], [prefix] =
>>> > Left(ni_number,2)) =1
>>> >
>>> > ... but I don't think I could use this at table level, and in any
case
>>> > I
>>> > suspect there is a better way!!
>>> >
>>> > Hope someone can help.
>>> > Many thanks
>>> > Leslie Isaacs
>>> >
>>> >
>>> >
>>>
>>>
>>
>>
>
>

Hi Les

As Ted said you can put the first and last characters in a table and as Jeff
said use the Left, Mid and Right to get the parts you want to compair. And
then use either a recordset or DLookup to compair the characters

Dim varX As Variant
varX = DLookup("[FirstCharacters]", "NIFirstCharacters", "[FirstCharacters]
= ' " & Left(txtNIEnter,2])

if varX is null then
' not found ...
- that should get you started BUT..

My issue and reason for commenting is that you say that you have 3 forms
that need to have this checked. Unless you have 3 tables that you are
putting "Different" NI in then you don't need to have the code checked each
time.

One form should be the "People" form where you enter the NI the first time -
and any other forms you are useing the NI as the Lookup for the person then
it is just a combo box lookup with limit to list.

I disagree with Jeff about the lookup - the NI is a totally acceptable and
IMHO the best way to find people. It is simpler to enter the number and see
that the name is wrong because you entered it wrong, than looking through 20
John Smith's to find the correct NI number. You also don't have to ask for
the correct spelling, middle initial, dave or david.


"PayeDoc" wrote:

> Hello Ted
>
> Many thanks for your reply.
>
> I fully understand your comments about splitting the ni_number field, and
> that from my first description this field does seem to be 3 wrapped in 1:
> but in fact the 'ni_number' really is a single entity (it's an employee's
> National Insurance number, allocated by HM Government!!), and it would be
> quite cumbersome for users having to enter these values in 3 parts. I
> realise that I could make the inputting of the 3 parts reasonably 'seamless,
> but it would still mean more complexity on the form - and in fact there are
> 3 forms where these values are currently added, and umpteen reports where I
> would need to concatenate the parts ... so I'm pretty keen if possible to
> find a way of applying the validation at table level!!
>
> Can you see how I could do this?
>
> Thanks again
> Les
>
>
> "TedMi" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Sounds like your field is meant to hold three separate facts, which is a
> > violation of referential database principles. Recommend you create three
> > text fields of lenght 2, 6 and 1 respectively. Create a table of allowable
> > 2-letter combos and use it as the row source for a combobox picklist to
> > populate the 2-char field. Make the format of the 6-char field numeric and
> > allow user input. For the 1-char field, replicate the combobox process,
> with
> > rowsource being a table of allowable single letters. That way, if the
> > allowable alphas change, you can just change the tables, not your
> validation
> > code.
> > For displaying in forms and reports, you can concatenate the three fields
> in
> > a query.
> > -TedMi
> >
> > "PayeDoc" <(E-Mail Removed)> wrote in message
> > news:eIv5RI$(E-Mail Removed)...
> > > Hello All
> > >
> > > I have a field 'ni_number' in a table which must always be 2 alphas then
> 6
> > > digits then 1 alpha, and I have the validation for this in place. In
> fact,
> > > however, the first 2 alphas must be from a defined set of (I think it's
> > > around 150) allowable combinations, and the last alpha must be one of 6.
> > > What's the best way of adding this validation? Obviously I could just
> have
> > > a
> > > very long rule with 150 "Or"s, but that seems clumsy. I can think of
> > > creating a new table [ni_valid_data], with 3 fields (say 'ni_serial'
> > > (key),
> > > 'prefix' and 'suffix') corresponding to the 2 valid datasets, and then
> > > having a validation rule along the lines of
> > >
> > > DCount([ni_valid_data]![ni_serial], [ni_valid_data], [prefix] =
> > > Left(ni_number,2)) =1
> > >
> > > ... but I don't think I could use this at table level, and in any case I
> > > suspect there is a better way!!
> > >
> > > Hope someone can help.
> > > Many thanks
> > > Leslie Isaacs
> > >
> > >
> > >
> >
> >
>
>
>

Leslie

Rather than listing all 150 (and then having to maintain the list when it
later changes ?!?), consider using a lookup table as you've described.
Then, in your validation statement, see if you could use something like
(again, untested):

.... In (SELECT ValidNumber FROM tlkpValidFirstNumber)

and something similar for that last "digit".

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hello Craig

Many thanks for your response. I have used your suggested DLookup, and all
is well!

The reason for the 3 forms is that it has simply proved useful to allow
users to enter/edit NI numbers 'on the fly' when they are adding/editing
other employee data using 'minor' forms - rather than forcing them to go
back to the 'people' form: possible not good practice technically, but very
popular with users!

I understand your point about selecting the NI number and then checking
the name, and I may move to that. The difficulty would be that the
information that is initially presented is the name (clients refer to John
Smith, not employee AA123456B), so there would need to be some initial
cross-reference from name to NI number anyway!

Food for thought though - and I've now got the validation that I wanted,
so many thanks once again.

Les

"CraigH" <(E-Mail Removed)> wrote in message
news:76BB379A-B968-41C0-B96A-(E-Mail Removed)...
> Hi Les
>
> As Ted said you can put the first and last characters in a table and as
Jeff
> said use the Left, Mid and Right to get the parts you want to compair.
And
> then use either a recordset or DLookup to compair the characters
>
> Dim varX As Variant
> varX = DLookup("[FirstCharacters]", "NIFirstCharacters",
"[FirstCharacters]
> = ' " & Left(txtNIEnter,2])
>
> if varX is null then
> ' not found ...
> - that should get you started BUT..
>
> My issue and reason for commenting is that you say that you have 3 forms
> that need to have this checked. Unless you have 3 tables that you are
> putting "Different" NI in then you don't need to have the code checked
each
> time.
>
> One form should be the "People" form where you enter the NI the first
time -
> and any other forms you are useing the NI as the Lookup for the person
then
> it is just a combo box lookup with limit to list.
>
> I disagree with Jeff about the lookup - the NI is a totally acceptable
and
> IMHO the best way to find people. It is simpler to enter the number and
see
> that the name is wrong because you entered it wrong, than looking
through 20
> John Smith's to find the correct NI number. You also don't have to ask
for
> the correct spelling, middle initial, dave or david.
>
>
> "PayeDoc" wrote:
>
>> Hello Ted
>>
>> Many thanks for your reply.
>>
>> I fully understand your comments about splitting the ni_number field,
and
>> that from my first description this field does seem to be 3 wrapped in
1:
>> but in fact the 'ni_number' really is a single entity (it's an
employee's
>> National Insurance number, allocated by HM Government!!), and it would
be
>> quite cumbersome for users having to enter these values in 3 parts. I
>> realise that I could make the inputting of the 3 parts reasonably
'seamless,
>> but it would still mean more complexity on the form - and in fact there
are
>> 3 forms where these values are currently added, and umpteen reports
where I
>> would need to concatenate the parts ... so I'm pretty keen if possible
to
>> find a way of applying the validation at table level!!
>>
>> Can you see how I could do this?
>>
>> Thanks again
>> Les
>>
>>
>> "TedMi" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Sounds like your field is meant to hold three separate facts, which
is a
>> > violation of referential database principles. Recommend you create
three
>> > text fields of lenght 2, 6 and 1 respectively. Create a table of
allowable
>> > 2-letter combos and use it as the row source for a combobox picklist
to
>> > populate the 2-char field. Make the format of the 6-char field
numeric and
>> > allow user input. For the 1-char field, replicate the combobox
process,
>> with
>> > rowsource being a table of allowable single letters. That way, if the
>> > allowable alphas change, you can just change the tables, not your
>> validation
>> > code.
>> > For displaying in forms and reports, you can concatenate the three
fields
>> in
>> > a query.
>> > -TedMi
>> >
>> > "PayeDoc" <(E-Mail Removed)> wrote in message
>> > news:eIv5RI$(E-Mail Removed)...
>> > > Hello All
>> > >
>> > > I have a field 'ni_number' in a table which must always be 2 alphas
then
>> 6
>> > > digits then 1 alpha, and I have the validation for this in place.
In
>> fact,
>> > > however, the first 2 alphas must be from a defined set of (I think
it's
>> > > around 150) allowable combinations, and the last alpha must be one
of 6.
>> > > What's the best way of adding this validation? Obviously I could
just
>> have
>> > > a
>> > > very long rule with 150 "Or"s, but that seems clumsy. I can think
of
>> > > creating a new table [ni_valid_data], with 3 fields (say
'ni_serial'
>> > > (key),
>> > > 'prefix' and 'suffix') corresponding to the 2 valid datasets, and
then
>> > > having a validation rule along the lines of
>> > >
>> > > DCount([ni_valid_data]![ni_serial], [ni_valid_data], [prefix] =
>> > > Left(ni_number,2)) =1
>> > >
>> > > ... but I don't think I could use this at table level, and in any
case I
>> > > suspect there is a better way!!
>> > >
>> > > Hope someone can help.
>> > > Many thanks
>> > > Leslie Isaacs
>> > >
>> > >
>> > >
>> >
>> >
>>
>>
>>

Jeff

OK - got it! In fact I've used a lookup table and Craig's suggested
DLookup expression, and it works a treat.

Many thanks for your help (as ever!).
Les

"Jeff Boyce" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Leslie
>
> Rather than listing all 150 (and then having to maintain the list when
it
> later changes ?!?), consider using a lookup table as you've described.
> Then, in your validation statement, see if you could use something like
> (again, untested):
>
> .... In (SELECT ValidNumber FROM tlkpValidFirstNumber)
>
> and something similar for that last "digit".
>
> Regards
>
> Jeff Boyce
> Microsoft Access MVP
>
> Disclaimer: This author may have received products and services
> mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
>
> Any code or psuedocode included in this post is offered "as is", with no
> guarantee as to suitability.
>
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
>
>