Re: IIf statement in query criteria

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It would help if you showed the query you are trying to build and told us a little bit about the type of fields you are applying the criteria against.

SELECT Field1
FROM SomeTable
WHERE Field2 = Forms![NameOfForm]![NameOfComboSource]

If Field2 always has a value and is always text you can use:
SELECT Field1
FROM SomeTable
WHERE Field2 LIKE Nz(Forms![NameOfForm]![NameOfComboSource],"*")

Otherwise you can use
SELECT Field1
FROM SomeTable
WHERE Field2 = Forms![NameOfForm]![NameOfComboSource]
OR Forms![NameOfForm]![NameOfComboSource] Is Null


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Proko wrote:
I am having a problem with the iif statement when I use it in the criteria for a query for a combo box (Combo2) that is based on the selection of another combo box (Combo1)

Essentially, what I am trying to do is show all choices in combo2 if there has not been a selection in Combo1; And show a filtered selection in Combo2 if there is a selection in Combo1.

I am trying to do this using the iif statement when setting the criteria in the Combo2 query.

iif (isnull(combo1), No Criteria is Set, Criteria is set)

I am having trouble with the "No Criteria is Set" part of the expression. Leaving it blank, inserting Null or "" (zero length string" doesn't work.

Why am I doing this? Not only do I want to be able to make a selection in Combo1 then a selection in Combo2 based on Combo1's selection but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1.

I hope all this makes sense.

Is there a way? Or am I going about this the wrong way.

Any help would be greatly appreciated.

Thanks
.

Relevant Pages

  • IIf statement in query criteria
    ... I am having a problem with the iif statement when I use it in the criteria ... what I am trying to do is show all choices in combo2 if there ... has not been a selection in Combo1; And show a filtered selection in Combo2 ...
    (microsoft.public.access.queries)
  • Re: 2 combo boxes and 1 text box filtered
    ... Create a query that selects this field from Table1. ... To get Combo2 to be limited to only the selecte item, you will need to use code like this: ... Once the selection has been made in Combo1, I'd like Combo2 to show column 2 ...
    (microsoft.public.access.formscoding)
  • RE: IIf statement in query criteria
    ... what I am trying to do is show all choices in combo2 if there ... has not been a selection in Combo1; And show a filtered selection in Combo2 ... iif, No Criteria is Set, Criteria is set) ...
    (microsoft.public.access.queries)
  • RE: IIf statement in query criteria
    ... what I am trying to do is show all choices in combo2 if there ... has not been a selection in Combo1; And show a filtered selection in Combo2 ... iif, No Criteria is Set, Criteria is set) ...
    (microsoft.public.access.queries)
  • Re: Drop Down Boxes
    ... so you have a Form1 with Combo1 and Combo2 ... Alex Dybenko ... >> after selection in Combo1 was done ...
    (microsoft.public.access.modulesdaovba)