Date Calculations

[PlateSpin]

Hi

I have two dates in a form and need to know the difference between them in months & days. I've tried to use DateDiff but it's not cutting it for me or I don't know how to kick it into life.

So to clarify what I'm looking for is if the dates in question are 20/01/2008 & 10/08/2009 I would expect an answer of.... 18 Months 21 Days.

Cheers

 

[heedaf]

Try this

Dim date1 As Date
Dim date2 As Date
Dim date3 As String
Stop
date1 = #1/20/2008#
date2 = #8/10/2008#
date3 = DateDiff("d", date1, date2)

Date3 is a string since you are calculating the number of days difference. You need to then change date 3 back into the format you need.

Hope this helps,
Dewayne

 

[PlateSpin]

If I knew what you were on about heedaf (Dewayne) I'd join in hahahaha.
I'm a bit of a novice when it comes to Access so your help is greatful but means nothing to me. I was expecting something I'd add to the Control Source field of a text box.

 

[Brianwarnock]

Not sure what Dwayne was doing but he is correct in that you will need to write a function. Create a new module and paste the code below.

Function monthsanddays(date1 As Date, date2 As Date) As String
 mths = DateDiff("m", date1, date2)
 If Day(date1) > Day(date2) Then
 mths = mths - 1
 End If
 
 newdate = DateAdd("m", mths, date1)
 days = DateDiff("D", newdate, date2)
 
 MsgBox (mths & "   " & days)
 monthsanddays = mths & "   " & days

End Function

To use =monthsanddays(daye1,date2)

Brian

 

[PlateSpin]

Cheers Brian - works a treat, but...........

When I open the form that has the text box containing the formular a little window appears showing me the months total and todays date with an OK button. How do I stop this little window from appearing?

I need to put in the control source field as =monthsanddays([txtStartDate], date())
not sure this is relevant but what the hey.

 

[Brianwarnock]

OOps, didn't realise I'd left my test msgbox in the code, Delete the line
MsgBox (mths & " " & days)
Not sure why you are getting todays date.
The first date is the earlier date.

Brian

 

[PlateSpin]

Excellent, cheers Brian.

It works slicker than a greased monkey sliding down a pole.

 

[Brianwarnock]

Glad to help.