Select All as reult in an IIf statement

[Chip Coutts]

I have a form with an unbound pulldown. I would like for my query to return
only reords that are equal to the pulldown if a value is selected, or display
all records if no value is selected. To get around "Null" issues, I have
set the value of the pulldown ="All" OnOpen the form.

The best I have been able to do so far is:
IIf([Forms]![Formname]![Fieldname]<>"All",[Forms]![Formname]![Fieldname],"*")
will return records when I enter [Fieldname] data something different than
All. But if I enter All, i get no records.

Any insight?

 

[John Spencer]

You can try LIKE as the operator

LIKE
IIf([Forms]![Formname]![Fieldname]<>"All",[Forms]![Formname]![Fieldname],"*")

This will return all the records IF the records always have a value in
the field. One workaround with smaller datasets is to force the field
to have a value by using a calculated field to search against.


Field: SearchThis: [YourTable].[YourField] & ""
Criteria: LIKE
IIf([Forms]![Formname]![Fieldname]<>"All",[Forms]![Formname]![Fieldname],"*")


Another method is to enter the following in the criteria. Access will
reformat that when you save the query.
[Forms]![Formname]![Fieldname]="All" OR [Forms]![Formname]![Fieldname]

It will create a calculated column that is something like the following
Field: [Forms]![Formname]![Fieldname]
Criteria (Line1): "All"
Criteria (Line2): <<Blank>>


And will combine that with
Field: YourField
Criteria (Line1): <<Blank>>
Criteria (Line2): [Forms]![Formname]![Fieldname]

Of course if you have other criteria Access will build the structure to
include those criteria correctly.

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

 

[Chip Coutts]

John:

I used your first suggestion and it worked perfectly. The only problem that
I had was that there was not data in the field for every record, so I have
gone back and populated the current records with a "delfault" value, then set
a default value for the field in the table for all future records.

Thanks!

You can try LIKE as the operator

LIKE
IIf([Forms]![Formname]![Fieldname]<>"All",[Forms]![Formname]![Fieldname],"*")

This will return all the records IF the records always have a value in
the field. One workaround with smaller datasets is to force the field
to have a value by using a calculated field to search against.


Field: SearchThis: [YourTable].[YourField] & ""
Criteria: LIKE
IIf([Forms]![Formname]![Fieldname]<>"All",[Forms]![Formname]![Fieldname],"*")


Another method is to enter the following in the criteria. Access will
reformat that when you save the query.
[Forms]![Formname]![Fieldname]="All" OR [Forms]![Formname]![Fieldname]

It will create a calculated column that is something like the following
Field: [Forms]![Formname]![Fieldname]
Criteria (Line1): "All"
Criteria (Line2): <<Blank>>


And will combine that with
Field: YourField
Criteria (Line1): <<Blank>>
Criteria (Line2): [Forms]![Formname]![Fieldname]

Of course if you have other criteria Access will build the structure to
include those criteria correctly.

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

I have a form with an unbound pulldown. I would like for my query to return
only reords that are equal to the pulldown if a value is selected, or display
all records if no value is selected. To get around "Null" issues, I have
set the value of the pulldown ="All" OnOpen the form.

The best I have been able to do so far is:
IIf([Forms]![Formname]![Fieldname]<>"All",[Forms]![Formname]![Fieldname],"*")
will return records when I enter [Fieldname] data something different than
All. But if I enter All, i get no records.

Any insight?