Ignore blank search parameter with IIf statement

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • power2005
    New Member
    • Sep 2007
    • 8
    #1

    Ignore blank search parameter with IIf statement

    Hi Experts

    I'm having a problem with IIf statement and it's driving me crazy... i'm a total newbie and i've spent hours to make it work but to no avail...

    what I'm trying to do is to have a search form with several parameters, and i want to ignore the parameter when it's blank, i tried the following criteria

    IIf(IsNull([Forms]![Form1]![Text2]),"*",[Forms]![Form1]![Text2]) and
    IIf(IsNull([Forms]![Form1]![Text2]), Like "*",[Forms]![Form1]![Text2])

    however, when the parameter is empty nothing comes out, only when i fill in the parameter the search result comes out.

    just in case i'm not clear i made a simple sample file to illustrate my problem. I've attached the mdb file at

    http://www.mediafire.c om/?fymly9xtx0r

    it's a very simple file with two tables, one query and one form, i'm using the button in the form to test out the IIf statement in the query.

    Please help me!! Thanks in advance.

    power2005
    Tags: None
    • power2005
      New Member
      • Sep 2007
      • 8
      #2
      Hi Experts

      After further trial and error I found that the problem may lie with the "*" expression as in

      IIf(IsNull([Forms]![Form1]![Text2]),"*",[Forms]![Form1]![Text2]) and
      IIf(IsNull([Forms]![Form1]![Text2]), Like "*",[Forms]![Form1]![Text2])

      i try using
      IIf(IsNull([Forms]![Form1]![Text2]),"(some entry that match)",[Forms]![Form1]![Text2])

      and it returns result,

      but when i switch back to using "*" it return null result again.

      i've been scouring the web for solution but searching "*" in google doesn't seem to yield much.

      Many thanks!

        Comment

        • power2005
          New Member
          • Sep 2007
          • 8
          #3
          The correct syntax is

          Like IIf(IsNull([Forms]![Form1]![Text2]), "*",[Forms]![Form1]![Text2])

          instead of

          IIf(IsNull([Forms]![Form1]![Text2]), Like "*",[Forms]![Form1]![Text2]) -> Wrong!!

          I'm sorry to waste your time if you come in to help only to see I've found the solution, but hopefully this post will help someone like me who doesn't know about the correct syntax for the wildcard as believe it or not, i spent many hours just to figure this out!

            Comment

            • MMcCarthy
              Recognized Expert MVP
              • Aug 2006
              • 14387
              #4
              Yes the wildcard will only work with the LIKE statement and you have to put it outside the IIf statement.

              Sorry I didn't get to see this earlier. However, you have the satisfaction of having worked it out for yourself.

              Thank you for posting the solution to help others with a similar problem.

                Comment

                • Whizzo
                  New Member
                  • Feb 2009
                  • 44
                  #5
                  Excellent! Just what I was looking for! Thanks folks!

                    Comment

                    • FishVal
                      Recognized Expert Specialist
                      • Jun 2007
                      • 2656
                      #6
                      A similar thread:
                      How to show all values in a query iif criteria?

                        Comment

                        Previous template Next
                        Working...