Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    57

    Unanswered: IIF Function to return all rows?

    Hi,

    I currently have a query which gets a parameter from a simple form. I want the query to either return all records or the only the record for a specified customer from my combo box. I have a value in the combo box for each customer and also a value "ALL".

    In the criteria section of the customer field I have the following IIF statement...

    =IIF([Forms]![Input]![cbo_CustLookup].value = "ALL","Like '*' ", [Forms]![Input]![cbo_CustLookup].value)

    This doesn't work, when I execute the query and manually give my input of "ALL" when prompted I get no records. However when I type Like '*' in the criteria field I successfully get all my records...

    Ideas please...

    Thanks in advance!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Look at your resulting SQL statement with the ALL selection. It's probably invalid ... Let's see you query's SQL code as currently contructed ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Perhaps more along the lines of:

    Code:
    LIKE IIF([Forms]![Input]![cbo_CustLookup].value = "ALL","*", [Forms]![Input]![cbo_CustLookup].value)
    Alternatively you could offer instructions to your users re wild cards and then your query becomes simply:
    Code:
    LIKE [Forms]![Input]![cbo_CustLookup]
    Quote Originally Posted by Grand Poobah
    Access 2000/2002 and STRICTLY ADO tho I've been known to dabble in the lesser arts ...
    Lol. The lesser DAO you mean? How will Izy take that I wonder...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yes - and LIKE "*" doesn't necessarily return all rows (though I'd mention given the thread title)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2003
    Posts
    57

    Thanks

    Thanks! Moving the "Like" to the beginning did the trick!

Posting Permissions

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