By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Ignore blank search parameter with IIf statement

P: 8
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
Sep 13 '07 #1
Share this Question
Share on Google+
5 Replies


P: 8
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!
Sep 14 '07 #2

P: 8
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!
Sep 14 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Sep 14 '07 #4

P: 44
Excellent! Just what I was looking for! Thanks folks!
Apr 16 '09 #5

FishVal
Expert 2.5K+
P: 2,653

Post your reply

Sign in to post your reply or Sign up for a free account.