If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
data validadtion
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 |
#2
|
|||
|
|||
data validadtion
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" wrote in message ... 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 |
#3
|
|||
|
|||
data validadtion
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" wrote in message ... 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" wrote in message ... 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 |
#4
|
|||
|
|||
data validadtion
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#5
|
|||
|
|||
data validadtion
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#6
|
|||
|
|||
data validadtion
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" wrote in message ... 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" wrote in message ... 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 |
#7
|
|||
|
|||
data validadtion
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. |
#8
|
|||
|
|||
data validadtion
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#9
|
|||
|
|||
data validadtion
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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|