query from form with multiple criteria options

[tagteam]

I need to create a query that has multiple criteria selected from a form



For example. I will always have the customer name but then the user might also choose any one or more of the following, start date, end date, number etc

I have tried to create a select query and added the customer form element to the criteria but I am not sure how to do the rest because it will always have at least one more criteria but I am not sure which one and it may have up to 5 more criteria.
Any help is appreciated.

[June7]

Options:

1. parameterized query using LIKE operator with wildcard, review:
http://datapigtechnologies.com/flash...earchform.html
http://datapigtechnologies.com/flash...tomfilter.html
http://datapigtechnologies.com/flash...mtoreport.html

2. VBA code to constructed criteria string and set the form Filter and FilterOn properties, review: http://allenbrowne.com/ser-62.html

[tagteam]

Thank you that was very helpful, especially the first link. I did not know about setting a blank default value. I have created my form and query and everything is working EXCEPT when one of the search fields has a null or empty value in the database.

Is there some kind of if statement that can be used so that if a value is blank or null it does not get left out of the query results?
Thanks

[June7]

Options:

1. create fields in query with expression that converts the nulls to a string: Nz(fieldname,"")
then apply the wildcard criteria to those constructed fields

2. include additional criteria for each field: Or Is Null

[tagteam]

Thanks for the reply,
I tried the Nz like this Like Nz([Forms]![GUF_UpdateCurrLease_NOLeaseHistory]![RiderDate] & "*","") and applied it to only the fields that might have an empty or null value and it did not work, I still get one missing record

i also tried Like [Forms]![GUF_UpdateCurrLease_NOLeaseHistory]![RiderDate] & "*" Or is Null and that one worked for me.

Thanks ,I will repost if there are any other issues

[June7]

The Nz does not go on the form control parameter, it goes on the field in the query that the parameter is under.