Microsoft Access Date Functions
Using DateDiff and DateAdd to Determine Differences in Dates
Q: I need to calculate the number of months a person has
been alive, but here's the interesting part... if the person was
born before the 15th day of the month, I need to give them "credit" for
that whole month, otherwise I need to assume they were born on the first
of the following month. So, for example, someone born on 1/4/90 would be
calculated from 1/1/90, and someone born on 1/16/90 would be assumed
born 2/1/90. Make sense?
A: Interesting problem. There
are a couple of ways you could do this. Normally, you can just calculate
a "close enough" number of months someone was alive by just subtracting
the current date from their birth date and dividing by 30. Of course,
I've rounded the answer to 0 decimal places.
MonthsAlive: Round((Date()-[DOB])/30,0)
Assuming today is 3/25/2008, that would produce OK results...
A better way to do it would be to use the DateDiff
function to get an exact number of months difference between the two
dates:
MonthsAlive: DateDiff("m",[DOB],Date())
This gives you slightly different results, but they're more accurate
because DateDiff will take actual months into consideration - which ones
are 30 days, 28 days, 31 days, etc.
Now let's deal with your special condition. You need to say if someone
was born before the 15th, assume they were born on the 1st of that
month. Otherwise, the 1st of the next month. So, let's figure out what
DAY of the month they were born on with the Day() function. I'll call
this field my Effective Day, or EffDay for short.
EffDay: Day([DOB])
Now we can figure out the effective date of the start of their birth
month, which I'll call EffThis. This is an odd calculation. I'm
essentially subtracting a number of days from their birthdate equal to
the day of the month they were born on (so 19 days from 1/19/90, for
example). Then I'm adding 1 day to it (because 19-19 is zero... and we
need the 1st). Since I'm subtracting but using the DateAdd function I
have to multiply by -1 (there is no DateSubtract function).
EffThis: DateAdd("d",-1*[EffDay]+1,[DOB])
Now it's easy to calculate the first day of the following month, which
I'll call EffNext. Just add one month to the EffThis date.
EffNext: DateAdd("m",1,[EffThis])
Next, with a little IIF function, you can tell the query which date to
use for their effective date of birth, or EffDOB by looking at
the day of the month they were born on. In English, this says "if the
day of the month is less than 15, then the effective date of birth is
EffThis, otherwise it's EffNext."
EffDOB: IIf([EffDay]<15,[EffThis],[EffNext])
Finally, you can use the DateDiff function again to take the difference
in months between today and their effective date of birth.
MonthsOld: DateDiff("m",[effdob],Date())
There ya go. It's not hard finding the difference between two dates, but
that little special clause of yours is what makes this example tough to
do.
>>> Also, see my other Tip on
calculating date
and time differences.
| 
