Combo Box Value List/Query Criteria

  • Follow


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



0
Reply Michael 1/19/2008 2:57:48 PM

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" <info@homekeyinc.com> wrote in message 
news:Gdokj.13262$OC1.5840@newsfe20.lga...
> 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
>
>
> 


0
Reply Al 1/19/2008 3:11:39 PM

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





"Al Campagna" <newsgroups@comcast.net> wrote in message 
news:%23UA$X2qWIHA.4476@TK2MSFTNGP06.phx.gbl...
> 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" <info@homekeyinc.com> wrote in message 
> news:Gdokj.13262$OC1.5840@newsfe20.lga...
>> 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
>>
>>
>>
>
> 


0
Reply Michael 1/19/2008 3:36:31 PM

On Sat, 19 Jan 2008 09:57:48 -0500, Michael wrote:

> 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])
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
Reply fredg 1/19/2008 5:45:46 PM

3 Replies
872 Views

(page loaded in 0.049 seconds)


Reply: