Return multiple values from a selection in a combobox; IN clause?

Question

I have a combo-box on a form, with two selections.

When the selection is made, I want it to return multiple values to a query.

So for this:

WHERE (([mytable].myfield)=IIF(([Forms]![MyForm]![mycombo])="XYZ","A","B"))

I would want it to actually return something like this:

WHERE (([mytable].myfield)=IIF(([Forms]![MyForm]![mycombo])="XYZ",("A","C" ), ("B","D" ) ))

It looks like IN would be a good option, but I am not able to get it to work.

An example I saw was:

WHERE City IN ('Paris','London');

I got this to work with out error, but it is a single parameter:

WHERE [ mytable].myfield IN (IIF ( ([Forms]![ MyForm]![mycombo ])= "XYZ","A","B" ) )

But I get a "Syntax error (missing operator) in query expression..." for this:

WHERE [ mytable].myfield IN (IIF ( ([Forms]![ MyForm]![mycombo ])= "XYZ", ("A","C"),"B" ) )

Any ideas?

Answer 1

Hi, try with:

WHERE (((IIf([Pers_Fisica]>0,Day([Valuta]) In (13,20),Day([Valuta])=30))=True));

Ciao Mimmo

Answer 2

Maybe I am being greedy here, but, as you can see in this code, "00","01","02" and "09" are common with both the results in the IF statement.

(IIf (

[Forms]![FindFGSKU-LIM-Form]![cbxFGCat]="ACC",

[LocalInvMaster-FinishedGoods].Planner IN("00","01","02","09","20","22","23","25","27","28","29"),

(IIf

(

[Forms]![FindFGSKU-LIM-Form]![cbxFGCat]="APP",

[LocalInvMaster-FinishedGoods].Planner IN("00","01","02","09","10","17","18","19"),

"" ) ) ) )

I tried to add an AND to the second IF, but if that part is TRUE, it returns nothing?

My gut feeling is I am using the AND in the wrong place?

(IIf (

[Forms]![FindFGSKU-LIM-Form]![cbxFGCat]="ACC",

[LocalInvMaster-FinishedGoods].Planner IN("00","01","02","09","20","22","23","25","27","28","29"),

(IIf

(

[Forms]![FindFGSKU-LIM-Form]![cbxFGCat]="APP",

(

[LocalInvMaster-FinishedGoods].Planner IN("00","01","02","09","10","17","18","19")

AND

[LocalInvMaster-FinishedGoods].Description IN("FPL","INS","STOVE","TORCH")

),

"" ) ) ) )

Answer 3

Thanks for the reply, I appreciate it!

I apologize for not being knowledgeable about this, but I am not sure what you mean?

Probably moot though, as I was able to get the IN working using what Mimmo posted.

If you think I might be better off doing something different, could you give me a few keywords or phrases to search to better understand?

Answer 4

I had to play with this a little, and a phantom parentheses was a PIMA, but I got it to work!

Thanks so much!

I tested in a test query, then embedded it into an existing query, using an IF:

(IIf

(

[Forms]![FindFGSKU-LIM-Form]![cbxFGCat]="ACC",

[LocalInvMaster-FinishedGoods].Planner IN("00","01","02","09","20","22","23","25","27","28","29"),

(IIf

(

[Forms]![FindFGSKU-LIM-Form]![cbxFGCat]="APP",

[LocalInvMaster-FinishedGoods].Planner IN("00","01","02","09","10","17","18","19"),

""

)

)

)

)

Answer 5

If you are trying to create a stored query, I'm not sure you will get it to work. My suggestion would be to generate the SQL statement in code and run it from there.