Performing calcualtions from combo list

S

sendahook

John said:
-
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?-

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]
Click to expand...


John, that was perfect!! I tried your last idea about making a 2 field
combo box and it worked!! Many thanks!! My only problem now is getting
that calulated date to enter into the table. Now I've read about 3000
posts asking how to get calculated control fields from forms into their
table and everyone seems to think it is a bad idea. And I can see that
usually it probably would be for most calculations. But in my case, I
have a query that is based on that date that was calculated in the
form...and since the query is based on the table, it seems to me that I
need to get that calculated date into the table somehow. Any
suggestions?
 
J

John Vinson

it seems to me that I need to get that calculated date into the table somehow.
Click to expand...

Why?

If you can calculate the date at any time, based on the data which
exists in the table, then *you can calculate the date whenever you
need it*.

It *might* be necessary to store it, or it may be better to store this
date INSTEAD of one of the fields used to calculate it - but storing
redundant data which can be calculated from other fields in the record
is never a good idea.

Could you explain the various fields in the table, how they are used,
and the nature of the calculation?

John W. Vinson[MVP]
 
Top