Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    Unanswered: IIF Statement Returning All rows

    Hi I have a query where I pass it a value from a form. If the value on the form combo box is "All" then I don't want to restrict the query otherwise I want to restrict it to just the value selected in the form.

    Below is the "Criteria" field that contains the code.

    IIf(([forms]![frmDetail Reports Selection].[lstFilter]="All"),"*",[forms]![frmDetail Reports Selection].[lstFilter])

    The problem is that when the user selects "All" in the combo box the query returns no results.

    If however I change the "*" to an actual value in the field such as "W" then it works fine?

    Can anybody please explain how I can get it to show all rows when the user selects "All" in the combo box.

    Many thanks in advance.


  2. #2
    Join Date
    Aug 2003

    Re: IIF Statement Returning All rows

    In your table you don't have a "*" stored as a value in the field your are searching. The "*" means nothing special to your database, it is just a character like any other. What is the syntax of your query? One solution might be to compose a different query, with a different WHERE clause, when the user chooses the "All" option. A good place to do it is the Click event of the combo box.

  3. #3
    Join Date
    Sep 2003
    Thank you for the feedback.

    I just worked out what the problem was (after thinking about your response) I needed to put the entire IIF statement in a Like statement Like(IIF(etc)).

    This way when ALL was selected made the criteria box contain Like "*" which then returned all rows.

    Before it was simply trying to find those reecords that contained the value "*".


Posting Permissions

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