473,325 Members | 2,792 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Complex Validation Rules in Microsoft Access

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
7 5929
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
Perfect!!! It was exactly what I needed! Thanks heaps!
Jun 27 '08 #3
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Mike | last post by:
Note: My XML experience to date has (unfortunately) been limited to reading and thinking, rather than implementation. Anyway, I am in the process of trying to figure out the most efficient way...
3
by: Dalan | last post by:
I apparently need a bit of assistance regarding the structure of some validation code on the BeforeUpdate or AfterUpdate event on a form for several fields that need to controlled. I did search the...
7
by: Mathew Hill | last post by:
I am a beginner to the more technical aspects of Microsoft Access (2000) and was wondering if any one can help? I have a field in a table called: ADMIN NUMBER This field should have 4...
5
by: JIM.H. | last post by:
Hello, I have this validation expression: ^(?:(?:0?|1)|(?:0?|11)(?!\/31)|(?:0?2)(?:(?!\/3|\/29\/(?:(?:0||)00|(?:\d{2}(?:0||))))))\/(?:0?||3)\/\d{4}$ This is supposed to match MM/DD/YYYY it is...
1
by: Nick | last post by:
I was wondering other opinions on this topic... I am working on an n-tier application which may be broken out to different servers at a later date. How should I go about the validation? I would...
22
by: pbd22 | last post by:
hi. I am having probelms with an update statement. every time i run it, "every" row updates, not just the one(s) intended. so, here is what i have. i have tried this with both AND and OR and...
10
by: gweasel | last post by:
What is the best way to apply a Validation Rule - or rather, where is the best place to put it? Is there an advantage to putting it on the field in the table vs setting the validation rule on the...
7
ak1dnar
by: ak1dnar | last post by:
Hi, I got this scripts from this URL There is Error when i submit the form. Line: 54 Error: 'document.getElementbyID(....)' is null or not an object What is this error. Complete Files
3
by: Harlequin | last post by:
I must start this posing by making the point that I am NOT a VB programmer and I'm something of a Newbie to MS Access. I can program in a number of languages (Java, Javascript, PERL,PHP and TCL) but...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.