Dynamic Query Criteria and returning all records (*)

[Williams]

I have a form that I am using as a dialog box for users to select their
criteria prior to running a query or a report. One of the fields on the form
is a combo box. If a user selects "All" from the combo box, I do not want
criteria assigned to this field. For example the field is [Status] if they
select "Active" then active records would display. If they select "closed",
only closed records would display. But if they select "All" then I want all
status types to appear. I thought I could use an * in an IIf statement, but
it does not seem to work. Here is my function:

IIf([Forms]![F_Prompt_AgrTyp_Timeframe]![Cmbo_Status]="All","*",[Forms]![F_Prompt_AgrTyp_Timeframe]![Cmbo_Status])

 

[Ken Sheridan]

In the criteria row of the relevant column in query design view put (as a
single line) the following expression:

[Forms]![F_Prompt_AgrTyp_Timeframe]![Cmbo_Status] Or
[Forms]![F_Prompt_AgrTyp_Timeframe]![Cmbo_Status] = "All"

You'll find that after saving the query in design view, if you reopen it in
design view Access will have move things around. The underlying logic will
remain the same, bit you might find it tricky to add any further parameters
should you decide to do so. For this reason I would always recommend writing
and saving queries like this in SQL view.

The logic behind this is that if the user selects a value other than 'All'
only rows with that value will be returned by virtue of the first part of the
Or operation; if they select 'All' then all rows will be returned (subject to
the other parameters) because the second part of the Or operation evaluates
to true for every row.

There is also the possibility that a perverse user could set the combo box
to Null, so you might like to cater for that too with:

[Forms]![F_Prompt_AgrTyp_Timeframe]![Cmbo_Status] Or
[Forms]![F_Prompt_AgrTyp_Timeframe]![Cmbo_Status] = "All" Or
[Forms]![F_Prompt_AgrTyp_Timeframe]![Cmbo_Status] Is Null

Ken Sheridan
Stafford, England