Performing calcualtions from combo list

S

sendahook

Hello,
I know I read how to do this once and didn't pay attention to it
thinking I would never use it. Of course, now I need it and can't find
it again...
Anyway, I would like to calcuate a date based on a user chosen field
(value list) in a drop down combo box. ie... If the user chooses "3
days" in the TIMEACTIVE field, a calculation would be performed in the
ENDINGDATE field that would show 3 days in the future. If "1 week" was
chosen, then calculation for 7 days would be done and so on....

Now I know how to use the DateAdd function to acheive this:
DateAdd("d",3,Date()) would give me three days ahead, etc. My problem
is incorporating this into the control source properties of the
ENDINGDATE field. I've tried using "IIf" action, but so far no luck.
All I get is #Name? in the ENDINGDATE field. I think I tried something
like: IIf([TIMEACTIVE]=1 DAY, DateAdd("d",1,Date()) I'm sure I'm not
expressing the calcualtion right, but can't find any other examples to
base it off of. Any suggestions on how to get this to work?

Thanks in advance
 
J

John Vinson

I think I tried something
like: IIf([TIMEACTIVE]=1 DAY, DateAdd("d",1,Date()) I'm sure I'm not
expressing the calcualtion right, but can't find any other examples to
base it off of. Any suggestions on how to get this to work?
Click to expand...

If the combo box's bound column contains the text string "1 DAY", then
you need quotes around the name.

Actually I'd suggest turning this expression inside out, and using the
more versatile SWITCH function: e.g.

DateAdd("d", Switch([TIMEACTIVE] = "1 DAY", 1, [TIMEACTIVE] = "3
Days", 3, [TIMEACTIVE] = "1 Week", 7, ...), Date())

Or, better, base your Combo Box on a two-field table with a human
readable label in one column, and a number of days in the other, and
using that as the bound column of the combo box:

DateAdd("d", [TIMEACTIVE], Date())


John W. Vinson[MVP]
 
Top