By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,028 Members | 1,733 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,028 IT Pros & Developers. It's quick & easy.

Complex Validation Rules in Microsoft Access

P: n/a
Hi guys,
I would like to setup a validation rule for a database in microsoft
access that restricts data entry so that a certain field can only be
filled in if another field has a specific answer (that is selected via
a drop down list).

Example
Field1 - options are "In" or "Out"
Field2 - options are "Join" or "Not Joining"

I want a validation rule that only allows a user to select one of the
options in Field2 only when "In" is selected in Field1. If "Out" is
selected in Field1, then I don't want the user to be able enter data
into Field2 at all.

I'm not a computer illiterate but this is beyond me so f anyone could
help me out i would really appreciate it!!!!!!!!

Thanks! Sharsy
Jun 27 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On May 14, 9:03*am, sharsy <sh...@ptpartners.net.auwrote:
Hi guys,
I would like to setup a validation rule for a database in microsoft
access that restricts data entry so that a certain field can only be
filled in if another field has a specific answer (that is selected via
a drop down list).

Example
Field1 - options are "In" or "Out"
Field2 - options are "Join" or "Not Joining"

I want a validation rule that only allows a user to select one of the
options in Field2 only when "In" is selected in Field1. If "Out" is
selected in Field1, then I don't want the user to be able enter data
into Field2 at all.

I'm not a computer illiterate but this is beyond me so f anyone could
help me out i would really appreciate it!!!!!!!!

Thanks! Sharsy
Hi Sharsy
You can do this using a table-level validation rule in an Access
database. A table-level validation rule is different from a field-
level validation rule in that (a) it can check for values in
combinations of different fields, not just one field, and (b) your
record is checked against the rule at the time the record is saved,
not when you enter data the field(s) in question.

To set up a table-level validation rule, open your table in design
view and open the table property sheet (ALT+ENTER).
In the Validation rule property, enter the expression you want as your
validation rule.

Something like

IsNull([field2]) Or ([field1]="In")

should work for the case you describe.

You can also use the Validation Text property to specify a text
message that your end users would see if they tried to save a record
which doesn't meet the table validation rule.

Jun 27 '08 #2

P: n/a
Perfect!!! It was exactly what I needed! Thanks heaps!
Jun 27 '08 #3

P: n/a
On May 14, 12:06*am, sharsy <sh...@ptpartners.net.auwrote:
Perfect!!! It was exactly what I needed! Thanks heaps!
It was exactly what you !!!wanted!!! and is an example all that's
wrong with Access. The use of an extraneous, arcane property to
compensate for poor design results in "Perfect!!!". Uh Huh!
Jun 27 '08 #4

P: n/a
On May 13, 10:29*pm, lyle fairfield <lyle.fairfi...@gmail.comwrote:
On May 14, 12:06*am, sharsy <sh...@ptpartners.net.auwrote:
Perfect!!! It was exactly what I needed! Thanks heaps!

It was exactly what you !!!wanted!!! and is an example all that's
wrong with Access. The use of an extraneous, arcane property to
compensate for poor design results in "Perfect!!!". Uh Huh!
and a good design would be ?
Jun 27 '08 #5

P: n/a
On May 14, 6:35*am, Roger <lesperan...@natpro.comwrote:
On May 13, 10:29*pm, lyle fairfield <lyle.fairfi...@gmail.comwrote:
On May 14, 12:06*am, sharsy <sh...@ptpartners.net.auwrote:
Perfect!!! It was exactly what I needed! Thanks heaps!
It was exactly what you !!!wanted!!! and is an example all that's
wrong with Access. The use of an extraneous, arcane property to
compensate for poor design results in "Perfect!!!". Uh Huh!

and a good design would be ?
If the only options for a field are two Strings then one, (barring
further knowledge of the data), might speculate that a boolean field,
or long integer field related to candidate tables would be more
effective. It's efficient when common string data appears only once in
a db.
Access's special table properties are far away from database standards
and confuse the incidental user enormously as we find in many posts
here. Projects are difficult to work with and almost impossible to
convert to more rigorous platforms.
Jun 27 '08 #6

P: n/a
On May 14, 6:35*am, Roger <lesperan...@natpro.comwrote:
On May 13, 10:29*pm, lyle fairfield <lyle.fairfi...@gmail.comwrote:
On May 14, 12:06*am, sharsy <sh...@ptpartners.net.auwrote:
Perfect!!! It was exactly what I needed! Thanks heaps!
It was exactly what you !!!wanted!!! and is an example all that's
wrong with Access. The use of an extraneous, arcane property to
compensate for poor design results in "Perfect!!!". Uh Huh!

and a good design would be ?
I think Lyle's objection is justified. IMO, a good design would be to
have both table level validation and some kind of validation within
Access. But by table level validation I mean a SQL CHECK constraint
rather than the table property validation proposed by "Helen." Table
level validation would ensure valid data regardless of how or by which
program the data is entered. Within Access, a field validation rule
or validation code can be used to obviate situations where the last
line of defense -- table level validation -- would be invoked.

James A. Fortune
CD********@FortuneJames.com
Jun 27 '08 #7

P: n/a
The information in http://www.mvps.org/access/forms/frm0028.htm contains
specifics on how to implement, but the trick to what you want is to include
options in the second Combo Box only with a foreign key to "In" in the table
underlying the first combo box. And, of course, allow Field 2 to be filled
only from the second combo.

Larry Linson
Microsoft Office Access MVP

"sharsy" <sh***@ptpartners.net.auwrote in message
news:e4**********************************@v26g2000 prm.googlegroups.com...
Hi guys,
I would like to setup a validation rule for a database in microsoft
access that restricts data entry so that a certain field can only be
filled in if another field has a specific answer (that is selected via
a drop down list).

Example
Field1 - options are "In" or "Out"
Field2 - options are "Join" or "Not Joining"

I want a validation rule that only allows a user to select one of the
options in Field2 only when "In" is selected in Field1. If "Out" is
selected in Field1, then I don't want the user to be able enter data
into Field2 at all.

I'm not a computer illiterate but this is beyond me so f anyone could
help me out i would really appreciate it!!!!!!!!

Thanks! Sharsy

Jun 27 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.