IIf statement in query criteria

[Proko]

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

 

[John Spencer]

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

 

[KARL DEWEY]

I am trying to do this using the iif statement when setting the criteria in
the Combo2 query.
Try this --
Like iif (isnull([combo1]), "*", [combo1])
I do not see how making a selection in Combo2 will ever control Combo1.

 

[blindman]

I have done a similiar thing a while back, but no programming expert. Think
I did it all with tables and queries. Do something along the lines of having
three tables. table 1 and table 2 for combo1 and table 3 for combo2. Table1
has static data, table 3 and 2 get populated by update queries based on what
gets dirty on the form. That way you can update from either combo. This is
not exact and doing this from memory but it did work for me , just have a
play. I remember that me.refresh came in handy also.

 

[Proko]

Thanks Karl,

I'll give it a try when I get to work. Makes sense though!

Proko

the Combo2 query.
Try this --
Like iif (isnull([combo1]), "*", [combo1])
I do not see how making a selection in Combo2 will ever control Combo1.

--
Build a little, test a little.

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

 

[Proko]

Thanks,

I'll give your idea some thought.

Proko