Dealing with empty controls in WHERE clauses

[Guest]

hi to all
in my SELECT statement, I have a WHERE clause like this...

WHERE Region = Forms!RegionStats.cbxRegion
AND County = Forms!RegionStats.cbxCount
AND Zone = Forms!RegionStats.txtZone

The query works great when all three controls have values, but i would also
like it to work if only one or two controls have values. What is the best
way to 'neglect' empty controls so that any SELECTING is determined only by
the controls that actually have values?

 

[Damon Heron]

This might work. Check out the nz function in Help


WHERE Region = nz(Forms!RegionStats.cbxRegion)
AND County = nz(Forms!RegionStats.cbxCount)
AND Zone = nz(Forms!RegionStats.txtZone)

Damon

 

[Marshall Barton]

hi to all
in my SELECT statement, I have a WHERE clause like this...

WHERE Region = Forms!RegionStats.cbxRegion
AND County = Forms!RegionStats.cbxCount
AND Zone = Forms!RegionStats.txtZone

The query works great when all three controls have values, but i would also
like it to work if only one or two controls have values. What is the best
way to 'neglect' empty controls so that any SELECTING is determined only by
the controls that actually have values?

The crude way to deal with it is to add another criteria:

WHERE Region = Forms!RegionStats.cbxRegion
OR Forms!RegionStats.cbxRegion Is Null
. . .

I think the "best" way is to use code to construct the query
and only inlude the criteria that is specified. Then stuff
the SQL string into where ever you want to use it.