Report_Subtracting Date fields using ()Now

[ppoindexter]

Hello all

I need to get the difference between 2 date fields on a report
All records have a startdate but not all have an end date,

I need to place a IIF then (i think) in the control source of the text box on the report so that the difference in the two dates will be either:

1) between the two dates given ie. [startdate]-[enddate]

or when a enddate is not present

2) between the startdate and todays date ie.. [startdate]-[()Now]

thanks in advance

 

[oldaf294]

pp,
just off the top.
=iif(isnull([enddate]), datediff("d",[startdate],now()),datediff("d",[startdate],[enddate])
should give the number of days. If you want weeks then replace "d" with "ww"
jim

 

[RV]

2) between the startdate and todays date ie.. [startdate]-[()Now]

Always use Date(), never Now() as Now() includes a time stamp hence could result in unwanted data to be retrieved.

RV

 

[oldaf294]

arGHHH, i meant to change that but apparently, a cog slipped.

 

[ppoindexter]

many thanks to oldaf294 and RV
and happy thanksgiving!