Number of months between dates

I am trying to compute storage charges by getting the number of months
between dates and multiplying it by a rate. I cannot get a correct
number of months if the date is greater than a year ago. Here is the
formula I am using now to get the number of months: #ofMonths:
Month([dbo_tblOutBoundTrackingHeader].[closedate]-[dbo_tblInboundTrackingHeader].[createdate])-1.
It has worked for me until now because it never was greater than a
year before. Please help. I am a novice at this.

re: Number of months between dates

MMFBprez wrote:[color=blue]
> I am trying to compute storage charges by getting the number of months
> between dates and multiplying it by a rate. I cannot get a correct
> number of months if the date is greater than a year ago. Here is the
> formula I am using now to get the number of months: #ofMonths:
> Month([dbo_tblOutBoundTrackingHeader].[closedate]-[dbo_tblInboundTrackingHeader].[createdate])-1.
> It has worked for me until now because it never was greater than a
> year before. Please help. I am a novice at this.[/color]

In the debug window I entered
? datediff("m",Date(),Date()-396)

and it echoed back
-12

? datediff("m",Date(),Date()-396)
-13

? datediff("m",Date()-396,Date())
13

re: Number of months between dates

Try the DataDiff function
NumOfMonths: DateDiff("m",[CreateDate],[CloseDate])
You'll find information about the DateDiff function in the VBA help file.
Pres Alt + F11 to open the VBE window and then click on help.

Jeff


"MMFBprez" <tmcguire@mmforward.com> wrote in message
news:26131bdc.0410141116.273c9de8@posting.google.c om...[color=blue]
> I am trying to compute storage charges by getting the number of months
> between dates and multiplying it by a rate. I cannot get a correct
> number of months if the date is greater than a year ago. Here is the
> formula I am using now to get the number of months: #ofMonths:
>[/color]
Month([dbo_tblOutBoundTrackingHeader].[closedate]-[dbo_tblInboundTrackingHea
der].[createdate])-1.[color=blue]
> It has worked for me until now because it never was greater than a
> year before. Please help. I am a novice at this.[/color]

re: Number of months between dates

If I read your formula correctly, it looks like you're 1st subtracting one
date from another (which yields days) and then using the month function on
the result, and then subtracting 1. This is a meaningless calculation. As
others have suggested, use the DateDiff() function. Or you could roll your
own with something like (air code):

Month(date1) - Month(date2) + (Year(date1) - Year(date2)) * 12

Fred


"MMFBprez" <tmcguire@mmforward.com> wrote in message
news:26131bdc.0410141116.273c9de8@posting.google.c om...[color=blue]
> I am trying to compute storage charges by getting the number of months
> between dates and multiplying it by a rate. I cannot get a correct
> number of months if the date is greater than a year ago. Here is the
> formula I am using now to get the number of months: #ofMonths:
>[/color]
Month([dbo_tblOutBoundTrackingHeader].[closedate]-[dbo_tblInboundTrackingHea
der].[createdate])-1.[color=blue]
> It has worked for me until now because it never was greater than a
> year before. Please help. I am a novice at this.[/color]