IIF Statement in a Criteria In() statment...?

[AccessARS]

In the query in questions there is a field where I need to place a
"In(Select...)" statement in the criteria but the catch is that the "Select
statement within the In() string would have to reference one of two queries
within the same database based on the value on an open form as indicated in
the IFF statement within...

In (IIf(([Forms]![frm_Period]![WDPoint]="Domestic")=True,"SELECT
qry_Chart2.Dash FROM qry_Chart2","SELECT qry_Chart2_Int.Dash FROM
qry_Chart2_Int"))


Unfortunately I do not seem to get any results even though the query or the
string does not error out?

I know my In(select) statement is good because it works without the IIF
statement ...but this is my 1st time doing something like this and I would
appreciate your feedback.

Thanks!

 

[NoodNutt]

G'day

Try

= IIf(([Forms]![frm_Period]![WDPoint]= "Domestic"),"SELECT
qry_Chart2.Dash FROM qry_Chart2","SELECT qry_Chart2_Int.Dash FROM
qry_Chart2_Int"))

HTH
Mark.

 

[John Spencer]

Your statement means that the query is looking to match one of the two strings
I doubt that tyour field contains
"SELECT qry_Chart2.Dash FROM qry_Chart2" OR
"SELECT qry_Chart2_Int.Dash FROM qry_Chart2_Int"

It might be better if you could modify one of the source queries based on the
value of WDPoint and use it in both cases.

If you can't you might try the following where clause.

WHERE (SomeField In (SELECT qry_Chart2.Dash FROM qry_Chart2) AND
[Forms]![frm_Period]![WDPoint]="Domestic")
OR
(SomeField in (SELECT qry_Chart2_Int.Dash FROM qry_Chart2_Int) AND
[Forms]![frm_Period]![WDPoint] & "" <>"Domestic")

If you don't use the sql window to build queries and only use the design view,
post back and I will try to describe how to set this up. Or better yet, post
back with the sql statement and we can try to modify it.

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