Criteria change

[Stewart]

I was wondering if there was a way to remove certain criteria from a select
query. My form contains three combo boxes (cboTrailNumber, cboPriority, and
cboCause), and a button that opens a report. My query contains criteria for
each combo box (i.e. Criteria: [Forms]![frmSS]![cboTrailNumber] for the
TrailNumber field). The report should show only those records that match the
criteria selected in the combo boxes, but the problem is you MUST select a
value for ALL combo boxes, trail number, priority, and cause. For example, I
want to be able to display all records with Trail Number "1" and Priority
"EM" without having to enter Cause; or just search for Priority "H". Please
help me eliminate the unwanted Criteria.

 

[John Spencer (MVP)]

You may be able to use the following scheme. Enter the criteria as follows, all
in one cell. Access will rearrange this and may make it too complex, but with
just three criteria this should work.

Field: TrailNumber
Criteria: [Forms]![frmSS]![cboTrailNumber] OR [Forms]![frmSS]![cboTrailNumber]
Is Null

Field: Priority
Criteria:[Forms]![frmSS]![cboPriority] OR [Forms]![frmSS]![cboPriority] is Null

Field: Cause
Criteria: [Forms]![frmSS]![cboCause] OR [Forms]![frmSS]![cboCause] is Null

If you decide to try this method, enter all three sets of criteria at once.

Another method that will work and is not as complex follows. The one
requirement is that the relevant field can NEVER be null.


Criteria: Like IIF([Forms]![frmSS]![cboTrailNumber] Is Null,"*",[Forms]![frmSS]![cboTrailNumber])