Trouble with Date Formats

[Guest]

My regional settings for Short Date is "dd/mm/yyyy". I wrote a function to
determine number of months between two dates based on the article at:

http://www.mvps.org/access/datetime/date0005.htm

but a "Type Mismatch" error occurs with this code:

Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"
Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
Global Const JetTimeFmt = "\#hh\:nn\:ss\#;;;\N\u\l\l"

Function NumMonths(dtDate As Variant) As Long
Dim StartDate As Variant
Dim EndDate As Variant

StartDate = Format(dtDate, JetDateFmt)
EndDate = Date

NumMonths = DateDiff("m", StartDate, EndDate)

End Function

Can someone tell me what's wrong with this ?
Also, I didn't quite understand those ";;;\N\u\l\l" in the formatting.
Please tell me what they are.

 

[Allen Browne]

The Format() function generates a string. If you already have date/time type
variables, you don't need to convert them to strings. (You only need the
date in string format if you are trying to put a literal date into a SQL
clause.)

Try something like this:

Function NumMonths(varDate As Variant) As Long
If IsDate(varDate) Then
NumMonths = DateDiff("m", varDate, Date)
End If
End Function

I also live in a d/m/y country, so here's what I know about how to solve the
3 cases where Access can get our dates wrong:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Guest]

Hitherto, I've used the plain vanilla "Datediff" to calculate number of
completed months between two dates but, I realised the results are not so
accuarate with different date values. I thought the Date Format might be the
reason.

My IE is unable to open the page at the Link you've given, I'll try again
later.

Thanks for the reply.

 

[Allen Browne]

Let me know if you still can't get the link working.

 

[Guest]

I got the link working now. May be temporary ISP problems. I tried also with
Google yesterday, the same thing happened. But, now everything is back to
normal.

Regarding the issue on hand, I hope 'dating' with Access 2007 will be a good
experience.

Thanks Allen, for the valuable tips. They are a treasure.