Combo Box Value List/Query Criteria

[Michael]

Hi Folks - I have a combo box with a value list of Option1, Option2 and ALL.
I pass this combo box info into a query. Here's a sample of the query
criteria:

IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is
Null,[Forms]![frmSwitchboard]![cboCaseType])

In other words, if the combo box selection is ALL, then display all records,
otherwise use selected option. The above criteria does not work. Any
suggestions? Thanks.

Michael

 

[Al Campagna]

Michael,
Try... (IsNull is an operator, not a value)

IIf([Forms]![frmSwitchboard]![cboCaseType]="All",
Null,[Forms]![frmSwitchboard]![cboCaseType])

You didn't indicate the name of the calculated field on the form that
contains the IIF statement, so I'll use [ResultFromIIF]
The CaseType field in your query should have a criteria of...
Like Forms!frmSwitchboard!ResultFromIIF & "*"

I think the Null will work, but I usually use "". I think either should
work, but didn't test.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Michael]

Hi Al - There is no calculated field on the form. The form has a combo box.
The combo box has 3 choices: Option1, Option2 and ALL. If they choose all, I
want the query to return all records.

The value of the combo box is passed into a query's criteria. I tried your
suggestion with NULL and "". Neither option worked. I don't think your Like
method will work with "al"l. That would just append * to ALL resulting in
criteria of ALL*. Make sense?

Note: I could instruct the user to leave the combo box blank, in which case
I could use the Like operator. But, I'd rather use ALL. Any ideas?

Michael

Michael,
Try... (IsNull is an operator, not a value)

IIf([Forms]![frmSwitchboard]![cboCaseType]="All",
Null,[Forms]![frmSwitchboard]![cboCaseType])

You didn't indicate the name of the calculated field on the form that
contains the IIF statement, so I'll use [ResultFromIIF]
The CaseType field in your query should have a criteria of...
Like Forms!frmSwitchboard!ResultFromIIF & "*"

I think the Null will work, but I usually use "". I think either
should work, but didn't test.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hi Folks - I have a combo box with a value list of Option1, Option2 and
ALL. I pass this combo box info into a query. Here's a sample of the
query criteria:

IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is
Null,[Forms]![frmSwitchboard]![cboCaseType])

In other words, if the combo box selection is ALL, then display all
records, otherwise use selected option. The above criteria does not work.
Any suggestions? Thanks.

Michael

 

[fredg]

Hi Folks - I have a combo box with a value list of Option1, Option2 and ALL.
I pass this combo box info into a query. Here's a sample of the query
criteria:

IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is
Null,[Forms]![frmSwitchboard]![cboCaseType])

In other words, if the combo box selection is ALL, then display all records,
otherwise use selected option. The above criteria does not work. Any
suggestions? Thanks.

Michael

Why IsNull as the False part of the IIf() expression?

Try:
Like IIf([Forms]![frmSwitchboard]![cboCaseType]="all","*",
[Forms]![frmSwitchboard]![cboCaseType])