show all records if the user leaves the input field blank

[nv]

I've made a form to ask the user to choose filter options for a report (begging date,end date, client nº,etc). I've also created the query that looks at the form for the filter option and returns the results that will appear on the report

It works, but if any field in the form is left blank the query will show only the records were that field is blank.

How can I make the query filter only the non-blank field son the form, and assume that the blank fields mean: "show all records"



Ex: if in "product" field the user chooses "apple"
and in the "client" filed the user leaves it blank

I wanted the result to be : - look for apple for all clients,

instead of look for apple for client = blank

 

[THoey]

You are going to need to use the ISNULL function to test for a value in the field and only add it to your WHERE clause if it is NOT null. Post you current query and we can help you out... Terry M. Hoey

 

[BLPandAJF]

I have some similar forms and queries. They way I accomplished this was as follows:

My forms have an unbound text box for users to input criteria (for your example, product).

The criteria for the field product in the underlying query is as follows:

Like Form!(form name)!(unbound text box name) & "*"

Then if the user doesn't enter apple or any other characters, it is still searching with the wildcard and all records will appear. Also, if your user is lazy and just enters "app", they will still see apples.

 

[jwelt]

I've been having the same problem, and even though the "*" works for all records that contain some data, it wont bring up any records that are, indeed, blank in that field. For example, if there was a record that contained "apple" without a client specified, it wouldn't bring it up. Is there anyway to bring them up too?