Number of months between two dates

[Guest]

Hello,
I have some VBA code which is calculating fees based on a number of
different criteria. The part that I am stuck on is to do with the number of
dates between a field called DateReceived and DateDueToCommence:
If the DateReceived is less than one month from the DateDueToCommence then
the fee is 2500.
If the DateReceived is between one and two months from the DateDueToCommence
then the fee is 1250.
If the DateReceived is more than two month from the DateDueToCommence then
the fee is 300.

At the moment I have a demo working that checks for values < 30, between 30
and 60, > 60. Obviously this is not correct and I have no idea how to solve
the problem. I have tried to use the DateDiff() function returning the number
of months but whatever I'm doing with it, it doesn't give me exactly what i
need.

Please help
Anon

 

[Douglas J. Steele]

What's your definition of month, and how is it different from what DateDiff
is giving you?

Since I have to guess, I'm going to say you're complaining about the fact
that DateDiff('m", #2007-06-30#, #2007-07-01#) returns 1, when it's only a
single day, not a month, between the two. That's how DateDiff works! You can
use the same sort of approach that's used to calculate a person's age given
their DOB. That's:

DateDiff("yyyy", DOB, Date) - IIf(Format(Date, "mmdd") < Format(DOB,
"mmdd"), 1, 0)

In your case, you'd want something like

DateDiff("m", DateReceived, DateDueToCommence) - IIf(Day(DateDueToCommence)
< Day(DateReceived), 1, 0)