True, False, or All

P

ProteanBeing

I have a query that I want to run from a form. If you select All for each
field (yes/no fields) it will filter for those values. If I select All how do
I make it not filter?
 
M

Michel Walsh

Write a dynamic query: Build a string which will represent the SQL statement
to be executed, without the filter you don't want to apply, then, assign
that string to a record source, or as argument of a recordset, or as it fits
in your case.

Alternatively, use a sequence of AND and OR, like:

WHERE ( (NOT FilterOneIsActive) OR Field1 = valueForFilterOne )
AND ( ( NOT FilterTwoIsActive) OR Field2=valueForFilterTwo)
AND ...


which can be shortened a little bit, using IMP, to:

WHERE ( FilterOneIsActive IMP field1 = valueForFilterOne )
AND ( FilterTwoIsActive IMP field2 = valueForFilterTwo )
AND ...



but these generic solutions will be slower than the dynamic query.



Vanderghast, Access MVP
 
P

ProteanBeing

My original statement was incorrect. The user needs the option to select TRUE,
FALSE, or ALL.
The records will be filtered if TRUE or FALSE. When ALL is selected all of
the records are shown.
 
P

ProteanBeing via AccessMonster.com

My original statement was incorrect. The user needs the option to select TRUE,

FALSE, or ALL.
The records will be filtered if TRUE or FALSE. When ALL is selected all of
the records should be shown.
 
M

Michel Walsh

WHERE ( (valueForFilterOne='all') OR Field1 = valueForFilterOne )
AND ( (valueForFilterTwo='all') OR Field2 = valueForFilterTwo )
AND ...



Vanderghast, Access MVP
 
Top