Selected Criteria or All

[FBxiii]

Hi. I have a form that runs reports depending on criteria selected by the
user.

The form contains a Combo Box that contains 'Types'. I have populated the
combo box with the different available 'types' and an 'All Types' option.

In the query, I have placed the following in the Criteria for the SAR_Type
field.

IIf([forms]![frmSAR_Statistics]![cmbSAR_Type]<>"All
Types",[forms]![frmSAR_Statistics]![cmbSAR_Type],"*")

What I am trying to achieve is for the query to show either the Selected
'Type' or All Types. So the IIf statement will either take the value from
the Combo box, or just take all values.

It doesnt seem to work so I think I may be missing something.

Can anyone help?

Cheers,
Steve.

 

[John Spencer]

That should work if you have the like operator ANDif you have no nulls in
the SAR_Type field.

LIKE IIf([forms]![frmSAR_Statistics]![cmbSAR_Type]<>
"All Types",[forms]![frmSAR_Statistics]![cmbSAR_Type],"*")

Another method of handling this is to use
Field: Sar_Type
Criteria: [forms]![frmSAR_Statistics]![cmbSAR_Type] = "All Types" OR
[forms]![frmSAR_Statistics]![cmbSAR_Type]

When you save the query, Access will restructure it and add a calculated
field
Field: [forms]![frmSAR_Statistics]![cmbSAR_Type] = "All Types"
and add criteria rows where one row will have false for the expression and
the next row will have true for the expression

If this is the only field that you are applying criteria against you will
end up with two criteria rows.
If you are applying criteria to multiple fields but they are all on the same
criteria row (all AND conjunctions) then you will also end up with two rows.

HOWEVER, if you are using many OR's in the criteria, this query when
restructured rapidly becomes very complex -- to the point that you may get
an error message.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[FBxiii]

Thanks, I used the second method you gave me.

On the same note, I have a 'Select customer' combo box. I want to be able
to do the same with this field. Will this confuse things too much?

On the same note, is it possible to refer to selections in a list box in a
query?

Steve.

That should work if you have the like operator ANDif you have no nulls in
the SAR_Type field.

LIKE IIf([forms]![frmSAR_Statistics]![cmbSAR_Type]<>
"All Types",[forms]![frmSAR_Statistics]![cmbSAR_Type],"*")

Another method of handling this is to use
Field: Sar_Type
Criteria: [forms]![frmSAR_Statistics]![cmbSAR_Type] = "All Types" OR
[forms]![frmSAR_Statistics]![cmbSAR_Type]

When you save the query, Access will restructure it and add a calculated
field
Field: [forms]![frmSAR_Statistics]![cmbSAR_Type] = "All Types"
and add criteria rows where one row will have false for the expression and
the next row will have true for the expression

If this is the only field that you are applying criteria against you will
end up with two criteria rows.
If you are applying criteria to multiple fields but they are all on the same
criteria row (all AND conjunctions) then you will also end up with two rows.

HOWEVER, if you are using many OR's in the criteria, this query when
restructured rapidly becomes very complex -- to the point that you may get
an error message.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. I have a form that runs reports depending on criteria selected by the
user.

The form contains a Combo Box that contains 'Types'. I have populated the
combo box with the different available 'types' and an 'All Types' option.

In the query, I have placed the following in the Criteria for the SAR_Type
field.

IIf([forms]![frmSAR_Statistics]![cmbSAR_Type]<>"All
Types",[forms]![frmSAR_Statistics]![cmbSAR_Type],"*")

What I am trying to achieve is for the query to show either the Selected
'Type' or All Types. So the IIf statement will either take the value from
the Combo box, or just take all values.

It doesnt seem to work so I think I may be missing something.

Can anyone help?

Cheers,
Steve.

 

[John Spencer]

On the same note, I have a 'Select customer' combo box. I want to be able
to do the same with this field. Will this confuse things too much?

Response:
Probably NOT. The best way to find out is to try it and see. I would
suggest that you make a copy of the existing query and experiment with it.
If if works then you can get rid of the old query and rename the new query
with the old query's name

On the same note, is it possible to refer to selections in a list box in a
query?
Response:
If the list box is multi-select the answer is NO, if the listbox allows only
one item to be selected the answer is yes. So, if the listbox allows
multiple selections you need to use VBA to construct the query based on the
selected items

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[FBxiii]

Thanks for the reply.

I have attempted to create a function that builds an 'In' statement using
the selections but this wasnt working.

I have asked about this the posting called "Using code to build criteria".