Software

Ask something
Discussion Locked

Access - Calculating "date" fields

0 helpful
2 replies

by areaxi

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need to create a report or querie based expression that calculates the time elapsed between a "Start" date and and "End" date.

I need the expression to calculate that if start date was 1/1/02 and end date was 3/1/03 then 14 months have elapsed between the two dates.

"Thank you" to anyone able/willing to help!

2 replies

by stevecottle

I put together a function that should do what you need. Cut and paste the function into a module. Then you can create an expression in your query or report using the function.

Function fnMonths(dtOne As Date, dtTwo As Date)
fnMonths = (Year(dtTwo) - Year(dtOne)) * 12
fnMonths = fnMonths + Month(dtTwo) - Month(dtOne)
If Day(dtTwo) < Day(dtOne) Then
fnMonths = fnMonths - 1
End If
End Function

I built a little database with a record number, Date1 and Date2. Then I added a query that has all three fields and the expression "Months: fnMonths([Date1],[Date2])". The query returns the following result:

RecordNum Date1 Date2 Months
1 04/01/2003 08/20/2003 4
2 04/01/2003 08/20/2004 16
3 04/20/2003 05/01/2003 0
4 04/20/2003 05/01/2004 12
5 08/01/2003 07/20/2004 11
6 08/15/2003 07/11/2004 10

by adca

Try the DateDiff function:

DateDiff("m",#1/1/2002#,#3/1/2003#)

But substitute your field names:

DateDiff("m",[StartDateFieldName],[EndDateFieldName])

This assumes your start and end date fields ARE date/time fields.

HTH,
Paul

This discussion is closed to new replies.