Form to act as a filter

[westoby1989]

I'm trying to develop a form for users to be able to filter through all customers entered into the database

I've written a query with every field of the customer table in. The idea is that in the filter form the user can enter as many criteria they want while leaving some fields blank. I tried the following formula in the criteria of the query:

IIf(IsNull([forms]![Customer filter]![Forenames]),([Customers].[Forenames]) Like "*",[forms]![Customer filter]![Forenames])

My theory behind this is that if the user enters anything in that particular field then it filters the results however if the field is left blank the criteria allows anything using the "*" wildcard symbol.

But it doesn't seem to work though. Any suggestions?

 

[PHV]

You wanted something like this ?
SELECT ...
FROM ...
WHERE Customers.Forenames & "" Like [forms]![Customer filter]![Forenames] & "*"

Or like this ?
SELECT ...
FROM ...
WHERE (Customers.Forenames = [forms]![Customer filter]![Forenames] OR [forms]![Customer filter]![Forenames] IS NULL)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886