Filtering values

[Guest]

I have a criteria form to allow users to chose various info from dropdowns
that will be used as filters for a query. For example, if a user selects a
site from my cboSite field on my form and then opens the query, it is
filtered by this value. However, I also want to let the user leave this field
blank on my form to show all Sites.

This is the query field criteria I have used in the past that seems to work
okay:

Like "*" & [Forms]![frmUserPrepReadinessBySiteAndFunctionCriteria]![cboSite]

The Like "*" is what allows all sites to show if the form field is left blank.

Is there a better way to do this? I think my way might cause some issues,
like if one site name ("ABC") is part of another site name ("ABCDEF").

Thanks in advance for your reply,
Clint

 

[Allen Browne]

Switch your query to SQL View (View menu, in query design.)

You will see part of the WHERE clause that looks like this:
[Field1] Like "*" &
[Forms]![frmUserPrepReadinessBySiteAndFunctionCriteria]![cboSite]

Change it to:
([Forms]![frmUserPrepReadinessBySiteAndFunctionCriteria]![cboSite] Is
Null OR Field1 =
[Forms]![frmUserPrepReadinessBySiteAndFunctionCriteria]![cboSite])

Now if the combo is blank, the expression returns True (i.e. it is true for
all records.) If the combo is not blank, the expression is only true for the
records that contain the exact value.

Particularly where you have several fields that have this kind of criteria,
this whole approach becomes unweildy and inefficient. A better approach is
to create the WHERE clause dynamically, using only the boxes where the user
actually ended criteria. There's a downloadable example (without
explanation) here:
http://allenbrowne.com/unlinked/Search2000.zip
Requires Access 2000 or later.