Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Record Selection in Single Query (Access 2002)

    I have a table contiaining various fields along with four logical (Yes/No) fields. I have been trying without success to set up a query that selects records based on the logical fields. I created a main menu form that contains four check boxes. I am trying to implement a single query that will display data when one or more of the check boxes is checked and these match the "yes" state of the fields in the table. I did this by setting the query condition in each case to something like this: IIF(forms.frmMainMenu.chkBox1 = yes, yes, no)
    I thought that this would set either yes or no in the query conditions for each logical field. However, I see that this sets up an AND condition among these selections. The OR condition doesn't seem to work any better.

    The goal is to select all records that have any of the four logical fields matching any of the corresponding check boxes on the main menu form. Perhaps I'm trying to do the impossible, but it seems to me like there ought to be a way..

    Any suggestions on how to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record Selection in Single Query (Access 2002)

    In the (first) criteria line for the first Yes/No field, enter <code>[Forms]![frmMainMenu]![chkBox1]</code>
    In the (first) criteria line for the second Yes/No field, enter <code>[Forms]![frmMainMenu]![chkBox2]</code>
    In the (first) criteria line for the third Yes/No field, enter <code>[Forms]![frmMainMenu]![chkBox3]</code>
    In the (first) criteria line for the fourth Yes/No field, enter <code>[Forms]![frmMainMenu]![chkBox4]</code>

    Remarks:
    - There is no need to use IIf here. A check box is already True or False, so you don't need IIf to convert the value.
    - Note the use of ! and of square brackets.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Selection in Single Query (Access 2002)

    Hi Hans,

    That works, but it sets up an AND criteria. That is, when I have two of the boxes checked (say Box1 and Box2) the query only returns records that have boxes 1 AND 2 checked. I want the query to display all records that match ANY or ALL of the boxes checked. In other words, I want to query to return records that have only Box1, records that have only Box 2, and records that have both 1 & 2 checked.

    Changing to the OR condition doesn't seem to work either. I'm really baffeled by this one.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record Selection in Single Query (Access 2002)

    OK, remove the criteria you have now, and create a new calculated column in the query:

    [Field1] And [Forms]![frmMainMenu]![chkBox1] Or [Field2] And [Forms]![frmMainMenu]![chkBox2] Or [Field3] And [Forms]![frmMainMenu]![chkBox3] Or [Field4] And [Forms]![frmMainMenu]![chkBox4]

    where Field1, Field2 etc. are the names of the Yes/No fields. Clear the 'Show' check box for this column, and enter True in the criteria line.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Selection in Single Query (Access 2002)

    That works OK. I did have to add some more logic to select records that had none of the logic fields selected. So everything is working just fine. Thanks again for your suggestions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •