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!

Did you try using % instead of * ?

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.

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

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));