Parameter Queries

[mattster1010]

Hi All,

I have built a query that has multiple parameters. the parameters are employee name, date search (from & To) and department. Sometimes I do not want to input critirea for all three, when I dont the query returns no records. Is there anyway that the query can ignore null parameters but still return results for the parameters that do contain search critirea?

Cheers,

Mattster.

[CreganTur]

Is there anyway that the query can ignore null parameters but still return results for the parameters that do contain search critirea?

The short answer is 'no'.

When an input box appears asking for criteria and you click Ok without entering anything into it, it's exactly the same as entering 'Is Null' into the WHERE clause for that particular field (this would be okay if you wanted to see records where this field has a Null value).

There is a way to work around this, that I've found, but you have to be using a Form to choose parameters for the query, instead of supplying them to the standard Parameter inputboxes.

It would involve you creating different queries that require different parameters- save each one to a string variable. Then you run a Select Case that looks to see which parameters you are using, which then selects which SQL statement to use. The parameters on your form (probably in the form of textbox values) would be written into the SQL statement in the form of VBA variables.

This method takes more setup time, but would provide you with a more dynamic method of declaring parameters.