Wild card in IIF statement

[DaveWilds]

I am using a criteria entry form as the source of parameters for a select query, and I am using IIF statements in the query so that if the user does not want to specify for that particular field, the search still works based on the other parameters specified. Therefore the statement I am using is;

IIf([Forms]![Criteria Form]![Customer Account] Is Not Null,[Forms]![Criteria Form]![Customer Account],[Data]![Customer Account]).

This works.

However for some of the criteria I want to also use wild cards, so that the user can enter just part of the data, and the relevant records will be returned. I understand that wild cards cannot be embedded into the IIF statement above.

I have tried many methods, and trawled the web for ideas, but I cannot get this to work. I either want the user to enter the wild card themselves ie "100*" returning records starting with "100", or for the query to append the wild card itself.

Can someone help please?

 

[neileg]

You need to use the Like operator. This type of search form has been covered often in these forums.

 

[Brianwarnock]

Try this thread, It covers the use of Null and the like operator.

Brian

 

[DaveWilds]

Wild card in IFF statement

Thanks to the guys who replied so quickly. This is the first time I have used this sort of Forum and it is good to know there is help out there.

So, I have tried this code, and it still doesn't work.

IIf([Forms]![Criteria Form]![Customer Name] Is Not Null,Like "*" & [Forms]![Criteria Form]![Customer Name] & "*",[Data]![Customer Name.

What I want this do to is; where parameter field [Customer Name] is not null, use it at as a criteria (with wild cards), else return all records.

Is there another way of doing this?

You guys seem to be pros - sorry if this is a dumb thread!!

 

[Brianwarnock]

This is covered on the link I posted.

Brian

 

[neileg]

Try this sample posted by Jon _K
http://www.access-programmers.co.uk/forums/showthread.php?t=103312&highlight=search+null

 

[Brianwarnock]

Neil, that's the Link I posted earlier, he either didn't read it or dudn't understand it.

Brian

 

[neileg]

Neil, that's the Link I posted earlier, he either didn't read it or dudn't understand it.

Brian

Oops! Must read the posts fully.