How to specify a validation rule with multiple criteria

  • Thread starter Thread starter FL
  • Start date Start date
F

FL

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?
 
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.
 
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!
 
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 said:
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 said:
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.




.
Click to expand...
Click to expand...
 
Back
Top