Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query by Form Parameters (2000)

    (Dave and Rudi helped me earlier today- Thanks, but now the given solution isn't quite working.)

    Situation: I have a form where the user identifies criteria to be used in a query to return records to another form. Presently, this form consists of 3 option groups and 5 check boxes.

    Approach: Using [Forms]![FormName]![ControlName] in my query works, but only if I have values for each option group.
    I want the user to be able to select any number of choices (I know that option groups only allow 1 choice, but I am referring to whole option groups). For example, today I want to query based on criteria from option groups A and B. Tomorrow I only want to query based on option group C.

    Problem: If I use an Or: is null syntax, the query returns all the records, independent of the other option group selections.

    Isn't there a way that I can take only what was selected (and all of what was selected) from the form and run the query?

    Thanks in advance,

    Brandon.

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

    Re: Query by Form Parameters (2000)

    The attached screenshot shows two ways of doing this.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by Form Parameters (2000)

    This almost worked.

    I tried your sugguestion for one field and it seemed to work fine. I then applied it to the other two fields (responding to the option groups) and something bad happened to my query!

    Let me preface what happened with this: each field that is responding to the option group value is using an IIF statement to evaluate the option group value and thus return the proper values from the table (I don't know if that matters...).

    So now I have IIF statement all over the Or rows. Access broke each field's IIF into four rows! (that I don't understand.)

    Help?

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

    Re: Query by Form Parameters (2000)

    That is normal, expected behavior if you use the method from the upper half of the screenshot. Access will rearrange the criteria to take up 2^n rows, where n is the number of columns with criteria of this type. So for two columns, you get 2*2 = 4 criteria rows, for 3 columns, 2*2*2= 8 criteria rows etc.
    Access won't rearrange the criteria if you use the method from the lower half of the screen shot.

    If you want more help, you'll either have to provide detailed information, or post a stripped down version of your database (see <post#=401925>post 401925</post#> for instructions).

Posting Permissions

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