OK, guys, here I am again winth another puzzle!

I have a table, tblMyTable, where I have 12 fields, one for each month
(JanCost, FebCost, etc...), populated with some values, plus one named
Category.
I have created a form, frmMain, where I have a combo box, cboMonth, where I
can select one month.
I have a query where I want to bring my Category from tblMyTable plus one
field, depending on the month I select with my combobox.

I tried to define an expression in my query like:
Value: iif(Forms!frmMain.cboMonth = "Jan", JanCost,
iif(Forms!frmMain.cboMonth = "Feb", FebCost, iif(Forms!frmMain.cboMonth
=...)))

Well, it seems that this doens't work as I thought! Any idea what I can do
here?
The purpose is to create a report that shows the values for a specific month
and the YTD values (summarized, of course).

Any idea what can be done?

Thansk in advance,

L

You'll need to define what you mean by "doesn't work as I thought".

An easier way to do this, without all the nested IIfs, is to use Switch
function:

ValueResult: Switch(Forms!frmMain!cboMonth="Jan", JanCost,
Forms!frmMain!cboMonth="Feb", FebCost, Forms!frmMain!cboMonth="Mar",
MarCost, etc.)

Obviously, replace etc. with the rest of the "exchanges".

Don't use Value as the name of a field, either. It's a reserved word in
ACCESS, and you may confuse ACCESS when you use it.
--

Ken Snell
<MS ACCESS MVP>

"Laurontario" <laur (AT) ontario (DOT) ca> wrote