+ Reply to Thread
Results 1 to 8 of 8

Complex Validation Rules in Microsoft Access

  1. 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

  2. Re: Complex Validation Rules in Microsoft Access

    On May 14, 9:03*am, sharsy wrote:
    > 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.


  3. Re: Complex Validation Rules in Microsoft Access

    Perfect!!! It was exactly what I needed! Thanks heaps!

  4. Re: Complex Validation Rules in Microsoft Access

    On May 14, 12:06*am, sharsy wrote:
    > 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!

  5. Re: Complex Validation Rules in Microsoft Access

    On May 13, 10:29*pm, lyle fairfield wrote:
    > On May 14, 12:06*am, sharsy wrote:
    >
    > > 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 ?

  6. Re: Complex Validation Rules in Microsoft Access

    On May 14, 6:35*am, Roger wrote:
    > On May 13, 10:29*pm, lyle fairfield wrote:
    >
    > > On May 14, 12:06*am, sharsy wrote:

    >
    > > > 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.

  7. Re: Complex Validation Rules in Microsoft Access

    On May 14, 6:35*am, Roger wrote:
    > On May 13, 10:29*pm, lyle fairfield wrote:
    >
    > > On May 14, 12:06*am, sharsy wrote:

    >
    > > > 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
    CDMAPoster@FortuneJames.com

  8. Re: Complex Validation Rules in Microsoft Access

    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" wrote in message
    news:e47477bd-dc9b-4c53-b796-bb726af9b294@v26g2000prm.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




+ Reply to Thread