how to ignore blank criteria fields 2

[srenner]

Hi,

I have written a query which pulls several criteria from a form (called "Report Criteria Form")

The criteria / fields on my form are drop-down fields and user can OPTIONALLY use these fields. If the field is blank, then query should ignore it. If the field is not blank, then criteria should be used in query.

How would I set up my query to ignore form fields / criteria that are blank? Currently, the criteria line for one of fields in my query looks like this:

[Forms]![Report Criteria Form]![Ethnic Heritage]

Thanks for your help

 

[Frink]

Hallo,

You can use the IIf function. See help for more details on how it works.
For each filterable criteria field in the Query, put:

Like IIf(Len(nz([FormField],''))=0,[FieldName],[FormField])

Where FieldName is the name of the field and FormField is the full path to the filter field. ie, if you have a field called strEthnicHeritage in your query, then the criteria would be:

Like IIf(Len(nz([Forms]![Report Criteria Form]![Ethnic Heritage],''))=0,[strEthnicHeritage],[Forms]![Report Criteria Form]![Ethnic Heritage])

- Frink

 

[jaydeebetoo]

In the query design grid you would use this code in the criteria

[Forms]![Report Criteria Form]![Ethnic Heritage] Or Like [Forms]![Report Criteria Form]![Ethnic Heritage] Is Null

 

[srenner]

Thanks Frink and jaydeebetoo. Works like a dream!

Frink, I particularly liked your code. Saved me a bunch of time. God bless.

 

[PHV]

If you want to also get the records where the field and the combo are null:
=[Forms]![Report Criteria Form]![Ethnic Heritage] Or [Forms]![Report Criteria Form]![Ethnic Heritage] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244