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
5  4973 
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!
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!
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.
Excellent! Just what I was looking for! Thanks folks!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Phil Powell |
last post by:
The table already has a fulltext index and from there I can use the
MySQL fulltext search query to get results as well as the relevancy
score.
The problem I have is that MySQL has a default...
|
by: Rob Meade |
last post by:
Lo all,
I was just running through some code I was writing for a site and when it
came to the 'exact phrase' search type I wasn't sure whether that should run
through and ignore the words in the...
|
by: Emmett Power |
last post by:
Hi,
I have a form with a table with two fields SelectionID and Experience.
I am posting the data to a database using an array function. I have
set out the code below.
The problem I am having...
|
by: yanakal |
last post by:
Hi,
I'm using isql to query data and output the same to a flat file.
The isql has the following command options ' -h-1 -w500 -n -b -s"" '.
In the SQL_CODE, the first two lines before the select...
|
by: Andy |
last post by:
Hello,
I am having many problems with setting up a parameter query that
searches by the criteria entered or returns all records if nothing is
entered.
I have designed an unbound form with 3...
|
by: vbnetprogramer |
last post by:
How Can I Ignore Accsents and diacritical marks in WHERE statement?
i use sql statment for searching and i wont to search in text filds where some accent and diacritical marks have been enterd.
...
|
by: shira |
last post by:
I have done a fair bit of searching, but haven't yet been able to find
an explanation as to why one would set "ignore nulls" to "yes" when
creating an index. I understand what it does (I think),...
|
by: nickvans |
last post by:
I have a form in which users may search for a module based on a number of criteria including three check boxes (indicating which processes have been completed) as well as a text box for the user to...
|
by: sweeneye |
last post by:
Hi,
I'm basing a query on the variables used in a form. The database contains lots of problems, say with a computer and a tick box for the apropriate component like monitor, keyboard, mouse etc....
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
