Formula Error

[dinadvani via AccessMonster.com]

Hi All,

I am trying to calculate difference between two dates:
Projected date and actual date
But the problem is their are updates to projected date in another field
called as "New Date"

Now what i need is a formula that caluclates diff between projected date/new
date and actual date.

Diff Date: IIf([New Date]=" Is Null",[Projected Date],[New Date])

If we have a new date then i want system to override the projected date and
calculate the diff from new date to actual date, but if we dn't hve a new
date then i want the diff to be calculated between projected and actual dates.


Please help.

Regards,
D

 

[dinadvani via AccessMonster.com]

Please Help.....ASAP

Hi All,

I am trying to calculate difference between two dates:
Projected date and actual date
But the problem is their are updates to projected date in another field
called as "New Date"

Now what i need is a formula that caluclates diff between projected date/new
date and actual date.

Diff Date: IIf([New Date]=" Is Null",[Projected Date],[New Date])

If we have a new date then i want system to override the projected date and
calculate the diff from new date to actual date, but if we dn't hve a new
date then i want the diff to be calculated between projected and actual dates.

Please help.

Regards,
D

 

[Gary Walter]

I am trying to calculate difference between two dates:
Projected date and actual date
But the problem is their are updates to projected date in another field
called as "New Date"

Now what i need is a formula that caluclates diff between projected
date/new
date and actual date.

Diff Date: IIf([New Date]=" Is Null",[Projected Date],[New Date])

If we have a new date then i want system to override the projected date
and
calculate the diff from new date to actual date, but if we dn't hve a new
date then i want the diff to be calculated between projected and actual
dates.

following will get you "New Date" if it is not Null,
or "Projected Date" if it is Null

DiffDate: IIf([New Date] Is Null ,[Projected Date],[New Date])

of course, you would still then need to calc difference
between "DiffDate" and "Actual Date"

 

[Larry Linson]

Hi All,

I am trying to calculate difference between two dates:
Projected date and actual date
But the problem is their are updates to projected date in another field
called as "New Date"

Now what i need is a formula that caluclates diff between projected
date/new
date and actual date.

Diff Date: IIf([New Date]=" Is Null",[Projected Date],[New Date])

This will not work unless your "New Date" Field is a Text Field, and
contains the text " Is Null" if there is not a new date; and, if that were
the case, you'd rely on automatic or explicit type conversion to make it
useful in a calculation.

If, as seems reasonable from the format of calculation you posted, the
choosing of the date to use in calculating the difference is a Calculated
Field in a Query, Gary Walter's solution works. An alternate, which will
work also in VBA code with variables, as well as in a Query using Fields,
is:

DiffDate: IIf(IsNull([NewDate]),[ProjectedDate],[NewDate])

If we have a new date then i want system to
override the projected date and calculate the
diff from new date to actual date, but if we
don't hve a new date then i want the diff to be
calculated between projected and actual dates.

However, you should note that neither your calculation, nor Gary's
caclulates the difference between today's actual date and the projected or
new date. You can calculate that with a single statement...

TheDifference:
DateDiff("d",Date(),IIf(IsNull([NewDate]),[ProjectedDate],[NewDate]))

Larry Linson
Microsoft Access MVP