Complex Validation Rules in Microsoft Access

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • sharsy
    #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
    Tags: None
    • helenwheelss@yahoo.com.au
      #2
      Re: Complex Validation Rules in Microsoft Access

      On May 14, 9:03 am, sharsy <sh...@ptpartne rs.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.

        Comment

        • sharsy
          #3
          Re: Complex Validation Rules in Microsoft Access

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

            Comment

            • lyle fairfield
              #4
              Re: Complex Validation Rules in Microsoft Access

              On May 14, 12:06 am, sharsy <sh...@ptpartne rs.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!

                Comment

                • Roger
                  #5
                  Re: Complex Validation Rules in Microsoft Access

                  On May 13, 10:29 pm, lyle fairfield <lyle.fairfi... @gmail.comwrote :
                  On May 14, 12:06 am, sharsy <sh...@ptpartne rs.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 ?

                    Comment

                    • lyle fairfield
                      #6
                      Re: Complex Validation Rules in Microsoft Access

                      On May 14, 6:35 am, Roger <lesperan...@na tpro.comwrote:
                      On May 13, 10:29 pm, lyle fairfield <lyle.fairfi... @gmail.comwrote :
                      >
                      On May 14, 12:06 am, sharsy <sh...@ptpartne rs.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.

                        Comment

                        • CDMAPoster@fortunejames.com
                          #7
                          Re: Complex Validation Rules in Microsoft Access

                          On May 14, 6:35 am, Roger <lesperan...@na tpro.comwrote:
                          On May 13, 10:29 pm, lyle fairfield <lyle.fairfi... @gmail.comwrote :
                          >
                          On May 14, 12:06 am, sharsy <sh...@ptpartne rs.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
                          CDMAPoster@Fort uneJames.com

                            Comment

                            • Larry Linson
                              #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" <shari@ptpartne rs.net.auwrote in message
                              news:e47477bd-dc9b-4c53-b796-bb726af9b294@v2 6g2000prm.googl egroups.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

                                Comment

                                Previous template Next
                                Working...