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
|
|||
|
|||
How to specify a validation rule with multiple criteria
How can I specify a validation rule that will perform the following changing
validation. Example: Number in field must be between 8 and 12 for this record. Number in field must be between 18 and 22 for next record in same form. Number in field must be between 28 and 32 for next record in same form. The criteria for number range acceptability can change at any moment but the actual recording of number always goes into same field. If this is not achievable then how should it be set up? Typically, I would have many records with numbers falling into a single range (such as 8 to 12). However, the same form and table record data is used that requires a different range of numbers from time to time. Any suggestions? -- FL |
#2
|
|||
|
|||
How to specify a validation rule with multiple criteria
Access stores data in tables, sort of a "bucket o' data". There is no
readily discernable order. Thus, there's no built-in way for Access to know what YOU mean by "the next record" unless you explain it to Access in excruciating detail. You've described "how" you are trying to accomplish something, but not much on what that underlying business need is. If you'll provide a bit more description about that business need, folks here may be able to offer more specific suggestions. It would also help to have some idea of the domain we're talking about. Is the "number" a measurement? Of what? You appear to have records related to other records... how? More info, please... 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 pseudocode 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. "FL" wrote in message ... How can I specify a validation rule that will perform the following changing validation. Example: Number in field must be between 8 and 12 for this record. Number in field must be between 18 and 22 for next record in same form. Number in field must be between 28 and 32 for next record in same form. The criteria for number range acceptability can change at any moment but the actual recording of number always goes into same field. If this is not achievable then how should it be set up? Typically, I would have many records with numbers falling into a single range (such as 8 to 12). However, the same form and table record data is used that requires a different range of numbers from time to time. Any suggestions? -- FL |
#3
|
|||
|
|||
How to specify a validation rule with multiple criteria
Jeff,
Thanks for the reply. I'll try to be more descriptive of our application needs. We are recording a measurement. I have two tables. One table contains the product description and two fields that contain the minimum and maximum allowable range numbers. Such as [field 1 min value] and [field 2 max value]. As an example, for one product it will be "8.21" minimum and "12.57" maximum recorded in these fields. We have about 100 product descriptions that each contain their own min. and max values. Some products will have the exact same min. max. values. While others will be different range of acceptable numbers such as 18.33 to 22.67 or 13.22 to 15.43. The second table contains the actual data entry records that record the number value that must fall between the number range for it's product description category. The operator will select the product then record the measurement number value. This table collects a large number of records each day. So, I need a validation rule that will check each number entered in the field against the two min. max. values of the other table (described in first paragraph above). If the value entered is out of this range then the person entering data will be flagged by the validation text to notify the appropriate team member who can take action to correct for the accepted value. Hope this makes some sense. Thanks again! -- FL "Jeff Boyce" wrote: Access stores data in tables, sort of a "bucket o' data". There is no readily discernable order. Thus, there's no built-in way for Access to know what YOU mean by "the next record" unless you explain it to Access in excruciating detail. You've described "how" you are trying to accomplish something, but not much on what that underlying business need is. If you'll provide a bit more description about that business need, folks here may be able to offer more specific suggestions. It would also help to have some idea of the domain we're talking about. Is the "number" a measurement? Of what? You appear to have records related to other records... how? More info, please... 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 pseudocode 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. "FL" wrote in message ... How can I specify a validation rule that will perform the following changing validation. Example: Number in field must be between 8 and 12 for this record. Number in field must be between 18 and 22 for next record in same form. Number in field must be between 28 and 32 for next record in same form. The criteria for number range acceptability can change at any moment but the actual recording of number always goes into same field. If this is not achievable then how should it be set up? Typically, I would have many records with numbers falling into a single range (such as 8 to 12). However, the same form and table record data is used that requires a different range of numbers from time to time. Any suggestions? -- FL . |
#4
|
|||
|
|||
How to specify a validation rule with multiple criteria
Let me see if I can paraphrase this ...
Someone will be entering an actually-measured value for a product. If that actually-measured value is out-of-bounds (i.e., less than the minimum, or greater than the maximum) someone needs to be notified. If that's a fair re-phrasing, I suspect you can do this with a query. (but first, your [Product] table probably needs a unique identifier for each product) Your data-entry someone could use a combobox to pick the product for which s/he is entering a measurement. The AfterUpdate event could load a pair of unbound textboxes with the min and max values. The data entry person could enter the measured value, and the form could determine if the measured value is out of bounds. So if your intention is that the data entry person has to stop and notify someone each time a measurement falls out of bounds, that's a way to do it. On the other hand, what about the idea of having a heads-down data entry person just select a product, enter a measurement, and keep going. After all the ?day's? measurements are entered, you could run a report that ONLY displays the out-of-bounds measurements/products. "How" will depend on "what" you want to accomplish... Good luck! 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 pseudocode 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. "FL" wrote in message ... Jeff, Thanks for the reply. I'll try to be more descriptive of our application needs. We are recording a measurement. I have two tables. One table contains the product description and two fields that contain the minimum and maximum allowable range numbers. Such as [field 1 min value] and [field 2 max value]. As an example, for one product it will be "8.21" minimum and "12.57" maximum recorded in these fields. We have about 100 product descriptions that each contain their own min. and max values. Some products will have the exact same min. max. values. While others will be different range of acceptable numbers such as 18.33 to 22.67 or 13.22 to 15.43. The second table contains the actual data entry records that record the number value that must fall between the number range for it's product description category. The operator will select the product then record the measurement number value. This table collects a large number of records each day. So, I need a validation rule that will check each number entered in the field against the two min. max. values of the other table (described in first paragraph above). If the value entered is out of this range then the person entering data will be flagged by the validation text to notify the appropriate team member who can take action to correct for the accepted value. Hope this makes some sense. Thanks again! -- FL "Jeff Boyce" wrote: Access stores data in tables, sort of a "bucket o' data". There is no readily discernable order. Thus, there's no built-in way for Access to know what YOU mean by "the next record" unless you explain it to Access in excruciating detail. You've described "how" you are trying to accomplish something, but not much on what that underlying business need is. If you'll provide a bit more description about that business need, folks here may be able to offer more specific suggestions. It would also help to have some idea of the domain we're talking about. Is the "number" a measurement? Of what? You appear to have records related to other records... how? More info, please... 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 pseudocode 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. "FL" wrote in message ... How can I specify a validation rule that will perform the following changing validation. Example: Number in field must be between 8 and 12 for this record. Number in field must be between 18 and 22 for next record in same form. Number in field must be between 28 and 32 for next record in same form. The criteria for number range acceptability can change at any moment but the actual recording of number always goes into same field. If this is not achievable then how should it be set up? Typically, I would have many records with numbers falling into a single range (such as 8 to 12). However, the same form and table record data is used that requires a different range of numbers from time to time. Any suggestions? -- FL . |
Thread Tools | |
Display Modes | |
|
|