Confusing date difference problem

[Susan]

that would work if both dates were within the target
month. But to use the example, subtracting June 23rd from
may 14th would be more than 30 days, as would June 2ns
from July 8th.

 

[Rick B]

Susan:

You can subtract accross months. Access does not stop whenit hits 30.

You can do...

[EndDate] - [StartDate]

Even if the dates are 100 days apart, or 10 years apart.

Rick


that would work if both dates were within the target
month. But to use the example, subtracting June 23rd from
may 14th would be more than 30 days, as would June 2ns
from July 8th.

 

[Rick B]

FYI, my database has an age field for all the assignments we enter. We then
report them as <30 days, 30-90 days, 91-365 days, and over 1 year.


that would work if both dates were within the target
month. But to use the example, subtracting June 23rd from
may 14th would be more than 30 days, as would June 2ns
from July 8th.

 

[John Spencer (MVP)]

The calculation would need to do a comparison between the fields and the
parameter dates to determine which should be used. For illustration purposes, I
will give you some field names and some parameter names. You will have to
substitute your field names and parameter names in the proper places.

Fields: BeginAction and EndAction
Parameters: StartDate and EndDate

DateDiff("d", IIF(BeginAction<StartDate,StartDate,BeginAction),IIF(EndAction>EndDate,EndDate,EndAction))

 

[Susan]

Thanks for all the info, everyone....I'll work on it today!