Using LIKE operator in IIF function

[databuilt]

I am constructing a query for a form (and report) which has input parameters. One of the parameters is for the item number and the users want to enter partial data and return all matching items.

The problem I am having is in scripting the criteria.

I am using :
For field: IBLITM
criteria: IIf([ItemLike]<>&quot;&quot;,(Like [ItemLike]+&quot;*&quot,[IBLITM])

This returns all items wen the parameter field ([ItemLike]) is blank (OK so far), but returns no records when a (any valid begining string)parameter is given.

Conversly, if I just use: Like [ItemLike]+&quot;*&quot; as the crieria, I can return the appropriate results. Unfortunately, this requires the user to enter a string &quot;*&quot; to get all items.

Is there another way to get the IIF() working such that the user just user can just click OK (leaving the parameter field blank). Or... alternately defaulting the parameter to &quot;*&quot;??

 

[danvlas]

WHERE ((IBLITM Like [itemlike] & &quot;*&quot) OR ((IBLITM=IIf([itemlike]=&quot;&quot;,IBLITM Is Null Or IBLITM Is Not Null,[itemlike])))

That should do it...

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html