DateDiff function question

[mcbill20]

Hello all. I have a really basic question that I hope someone has a
better answer for. I apologize in advance-- I know this is probably a
really basic question but I am used to Oracle rathern than Access.

I have a database where they customer wants to purge records from
certain tables after three years. When I was asked to make the changes
I originally thought this was an incredibly simple thing to do. I
looked at the function list and assumed that a one line query using
DateDiff was the answer.

Unfortunately, the DateDiff function with the "yyyy" does not actually
calculate the number of years difference between two dates; it returns
the number of times the year portion of the date changes. For example,

DateDiff("yyyy",#31/12/2004#, #24/06/2005) returns 1
DateDiff("yyyy",#01/01/2005#, #24/06/2005) returns 0

even though the time difference is not a full year between the two
dates.

I know I can do days instead but then I have to add the calculations
for leap years, etc. and it just doesn't seem like a nice clean and
elegant way to do it. Is there a function or something that I am
missing?

Thanks.
Bill

[Wayne Morgan]

Let's try a different function instead.

Example:
DateAdd("yyyy", -3, Date())

You could also use DateSerial which splits the year, month, and date into
separate items.

Example:
DateSerial(Year(Date()) - 3, Month(Date()), Day(Date()))

You would then use the result of either of these in the criteria.

Example Criteria for the date field:
<=DateAdd("yyyy", -3, Date())

This will filter the query to only return records whose date is 3 years ago
or older.

--
Wayne Morgan
MS Access MVP
<mc******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hello all. I have a really basic question that I hope someone has a
better answer for. I apologize in advance-- I know this is probably a
really basic question but I am used to Oracle rathern than Access.

I have a database where they customer wants to purge records from
certain tables after three years. When I was asked to make the changes
I originally thought this was an incredibly simple thing to do. I
looked at the function list and assumed that a one line query using
DateDiff was the answer.

Unfortunately, the DateDiff function with the "yyyy" does not actually
calculate the number of years difference between two dates; it returns
the number of times the year portion of the date changes. For example,

DateDiff("yyyy",#31/12/2004#, #24/06/2005) returns 1
DateDiff("yyyy",#01/01/2005#, #24/06/2005) returns 0

even though the time difference is not a full year between the two
dates.

I know I can do days instead but then I have to add the calculations
for leap years, etc. and it just doesn't seem like a nice clean and
elegant way to do it. Is there a function or something that I am
missing?

Thanks.
Bill

[mcbill20]

Doh!
Boy do I feel stupid. :-)

Thanks. That works great.

Bill

[jimfortune]

mc******@yahoo.com wrote:

Hello all. I have a really basic question that I hope someone has a
better answer for. I apologize in advance-- I know this is probably a
really basic question but I am used to Oracle rathern than Access.

I have a database where they customer wants to purge records from
certain tables after three years. When I was asked to make the changes
I originally thought this was an incredibly simple thing to do. I
looked at the function list and assumed that a one line query using
DateDiff was the answer.

Unfortunately, the DateDiff function with the "yyyy" does not actually
calculate the number of years difference between two dates; it returns
the number of times the year portion of the date changes. For example,

DateDiff("yyyy",#31/12/2004#, #24/06/2005) returns 1
DateDiff("yyyy",#01/01/2005#, #24/06/2005) returns 0

even though the time difference is not a full year between the two
dates.

I know I can do days instead but then I have to add the calculations
for leap years, etc. and it just doesn't seem like a nice clean and
elegant way to do it. Is there a function or something that I am
missing?

Thanks.
Bill

This seems related to determining how old someone is given their
birthdate.

If you don't mind using the Format() function in a SQL string,

SELECT Int(Format([CDate],"yyyy.mmdd") - Format([BDate],"yyyy.mmdd"))
AS Age FROM tblBirthdays;

Originally I used Val() around the Format but I don't think I need it.
If you don't mind assuming True = -1,

SELECT Year([CDate]) - Year([BDate]) + IIf(Month([BDate]) =
Month([CDate]), Day([BDate]) > Day([CDate]), Month([BDate]) >
Month([CDate])) AS Age FROM tblBirthdays;

else

SELECT Year([CDate]) - Year([BDate]) + IIf(Month([BDate]) =
Month([CDate]), IIf(Day([BDate]) > Day([CDate]), -1, 0),
IIf(Month([BDate]) > Month([CDate]), -1, 0)) AS Age FROM tblBirthdays;

should be quite robust.

Note that the solution on Dev's site,

SELECT DateDiff("yyyy",[Bdate],[CDate]) + Int(Format([CDate],"mmdd") <
Format([Bdate],"mmdd")) AS Age FROM tblBirthdays;

only requires "mdd" rather than "mmdd" in both spots. BDate in
tblBirthdays stands for BirthDate and CDate stands for CurrentDate. I
envisioned something like Date() to be used in place of [CDate]. Be
sure to test any of these methods before use including tests like
3/1/03 to 2/29/04 that mess up simpler solutions that use the day of
the year. Perhaps some unforseen condition that messes up my solution
caused someone to split the calculation into two parts.

James A. Fortune

[Douglas J. Steele]

<ji********@compumarc.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

mc******@yahoo.com wrote:
Note that the solution on Dev's site,

SELECT DateDiff("yyyy",[Bdate],[CDate]) + Int(Format([CDate],"mmdd") <
Format([Bdate],"mmdd")) AS Age FROM tblBirthdays;

only requires "mdd" rather than "mmdd" in both spots.

Not so. You need the preceding 0 so that, for instance, February birthdays
are known to occur in October.

Format(#2/14/1990#, "mdd") will yield 214, while Format(#10/1/2005#, "mdd")
will yield 1001. Since we're comparing text values here, 214 is NOT less
than 1001. 0214 is less than 1001 though.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

[jimfortune]

Douglas J. Steele wrote:

Format(#2/14/1990#, "mdd") will yield 214, while Format(#10/1/2005#, "mdd")
will yield 1001. Since we're comparing text values here, 214 is NOT less
than 1001. 0214 is less than 1001 though.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

You're not comparing text values there. Try it out. SQL reads 0214 as
214. Maybe you're thinking of Format$.

James A. Fortune