IIF statement is acting up in a query

[andrewf10]

I have a query which is based on 2 tables (Log2000 & Customers) and criteria is entered by the user via a form called 'SearchMarkets'.

In concept, if the user chooses a 'Market' on this form, the query uses that value, otherwise it runs everything. My SQL looks like this:


SELECT Log2000.[Call ID], Customers.ID
FROM Customers INNER JOIN Log2000 ON Customers.CustomerNumber = Log2000.Customer
WHERE (((Customers.ID)=IIf([Forms]![SearchMarkets]![Market] Is Null,([Customers].[ID]) Like "*",[Forms]![SearchMarkets]![Market])));


For non-null values it works grand but the ([Customers].[ID]) Like "*" part isnt returning anything. However this piece of SQL does work when used on its own like this:

SELECT Log2000.[Call ID], Customers.ID
FROM Customers INNER JOIN Log2000 ON Customers.CustomerNumber = Log2000.Customer
WHERE ((([Customers].[ID]) Like "*"));



Can someone please help me overcome this issue?

Thanks in advance!

 

[jnixon]

Did you try using % instead of * ?

 

[mresann]

Try rewording it like this:

WHERE (((Customers.ID)=IIf(IsNull([Forms]![SearchMarkets]![Market]) or [Forms]![SearchMarkets]![Market]="",([Customers].[ID]) Like "*",[Forms]![SearchMarkets]![Market])));

Using the IsNull function within the query field is usually a better alternative than checking to see if the query field is equal to null, as you have written. In addition, the blank text field may NOT be null, but contain a zero length string, which is NOT the same thing as a null field. Adding the condition to check for that will allow you to truify your Iif statement in that case.

 

[andrewf10]

Thanks for your suggestions, tried both but still the same result. Head-wrecking stuff!

 

[andrewf10]

OK after LOTS of experimenting here's what seems to be working. Does this make sense?!

SELECT Log2000.[Call ID], Customers.ID
FROM Customers INNER JOIN Log2000 ON Customers.CustomerNumber = Log2000.Customer
WHERE ((([Customers].[ID]=[Forms]![SearchMarkets]![Market] Or [Forms]![SearchMarkets]![Market] Is Null)=True));