Calculation

[Rick]

I have 2 columns with dates in a table. BMT date and Exp date. I need to
claculate the overall survival in a 3rd column in the table whether it is:
BMT to EXP date (if patient still expired)
or
BMT to current date (if patient still alive)

Thanks

 

[Dennis]

You would not hold a calculated value in your table because 1 day later, the
calculations are wrong. Calculate on the fly using queries/forms/reports.
Using something like =IIf(IsNull([EXP Date]),DateDiff("d",[BMT
Date],Now()),DateDiff("d",[BMT Date],[EXP Date]))
Look in help for DateDiff options

 

[Jim Bunton]

Somethin like:

IIF DateDiff( "n", EXP, Now())>0 ), DateDiff("n",BMT, EXP), DateDiff(BMT,
Now() ) AS whatyouwanttocallit

means if minutes between EXP and Now is > 0 then minutes betwee BMT and EXP
elseminutesbetween BMT and Now()
[seconds is 's' hours 'h', days 'd' . . . lookup DateDiff on help]
You can transform the minutes into hours, days etc later.

Jim Bunton

 

[Rob Parker]

Hi Rick,

First, and most important, point is that you do NOT want to store this data
in another field (column) in your table. It is a calculated value, based on
the contents of two other fields; it should NOT be stored in the table, but
calculated "on-the-fly" as needed. You need a query which will include this
calculated value as a separate field, and you should base any forms/reports
on the query, rather than the table.

Set up a query based on your table, and add another field (column), with the
following expression:
SurviveDays:
IIf(IsNull([Exp]),DateDiff("d",[BMT],Date()),DateDiff("d",[BMT],[Exp]))

This assumes that the names of the fields in your table are "BMT" and "Exp",
respectively; if not, change the fieldnames in the expression. If you want
the survival time in a different format, change the "d" in the two DateDiff
expressions to the interval you require (and change the calculated fieldname
in the query to suit); see Help for the time intervals you can use - they
range from seconds to years ;-).

HTH,

Rob