How to use form combo box variable in query?

[rteeka]

I have combo box variable (RepFirm) in a form (WLOT Reports Selection). I am using this variable in criteria column of query to select the recrods. If nothing is selected from the combo box every thing should be selected and if any item is selected in the combo box, it should select only records matching to the value selected.

I am using the following code in query criteria. It is working only when I select a value, if I don't select anything in the combo box, nothng is selected from the table.

IIf([Forms]![WLOT Reports Selection]![RepFirm] Is Null,"",
[Forms]![WLOT Reports Selection]![RepFirm])

Thanks a lot in advance for the help.

 

[Guest_imported]

I use Access97 and I don't know what version you use. So it may be little different. But "IsNull" should be a funtion. You might want to do like :

IIf(IsNull([Forms]![WLOT Reports Selection]![RepFirm]),"",
[Forms]![WLOT Reports Selection]![RepFirm])

Good lukc.

 

[tlbroadbent]

If you put the IIF function in the criteria then Access will select records that match the criteria. Criteria will either be the text of the combo box or an empty string. Neither of these criteria will select "ALL" records.

Try the following in your criteria.

Like NZ([Forms]![WLOT Reports Selection]![RepFirm],"&quot & "*" Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[rteeka]

Hi tlbroadbent,

Your solution worked great! Thanks a lot for your help.

Hi Skylines,

I am using Access 2000, for some reason your solution did not work. Thanks for your advice.

This is a great forum!