Null Parameters

[Daniel Wilson]

I have a database for which each record can have any number of 21 checkboxes
selected. I have created a query to pull the values from the form unless the
value on the form Is Null. To do this I used the following statement in the
Criteria field of my Query in design view:
[Forms]![Search for AI - Specific]![parameter name] Or [Forms]![Search for
AI - Specific]![parameter name] Is Null

I need to return only the records with the exact same checkboxes selected as
those selected on the Query Parameter Form. This statement works fine, but
as there are 21 such check boxes ..I cannot open the query in design view
once created...when I try to...my system freezes and have to close Access.
The query works great...but it would seem it is too complex to open to make
adjustments...and have to recreate the query every time.
Can anyone suggest how I can simplify this query.

 

[Marshall Barton]

I have a database for which each record can have any number of 21 checkboxes
selected. I have created a query to pull the values from the form unless the
value on the form Is Null. To do this I used the following statement in the
Criteria field of my Query in design view:
[Forms]![Search for AI - Specific]![parameter name] Or [Forms]![Search for
AI - Specific]![parameter name] Is Null

I need to return only the records with the exact same checkboxes selected as
those selected on the Query Parameter Form. This statement works fine, but
as there are 21 such check boxes ..I cannot open the query in design view
once created...when I try to...my system freezes and have to close Access.
The query works great...but it would seem it is too complex to open to make
adjustments...and have to recreate the query every time.
Can anyone suggest how I can simplify this query.

I think you can avoid the query design window's limitations
by working on the query in SQL view.

OTOH, if you used code in the form's command button that
opens the query, you could use VBA code to construct the SQL
statement. This way the query would only have criteria for
the check boxes that are checked (or non Null?). The
general idea is to use code like:

If Me.chkbox1 = True Then
stWhere = stWhere & " And fld1"
End If

for each check box and then combining that with the SELECT
and FROM parts of the SQL statement. Then store the
complete SQL statement into a form's record source property
(or wherever you need it).