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.com/?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

re: Ignore blank search parameter with IIf statement

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!

re: Ignore blank search parameter with IIf statement

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!

re: Ignore blank search parameter with IIf statement

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.

re: Ignore blank search parameter with IIf statement

Excellent! Just what I was looking for! Thanks folks!

re: Ignore blank search parameter with IIf statement

A similar thread:
How to show all values in a query iif criteria?