Access Query

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I am trying to develop a query from a database that
calculates the age of a Little League ball player. We
know his existing birthdate and what we need to know is
how old he is in years on a specific date (July 31, 2004).

Thanks

Bob
 
Enter this in the Field: of the query ---

Age: DateDiff("Y",[BIRTHDATE],[Enter date])

Access dropped some functions. I just tried this and it
gave days, not years. If you get days, use this instead -
 
To get years, you use "yyyy" not "y".

TO get age accurately calculated as of a date.

DateDiff("yyyy",BirthDate,#7/31/2004#) +
Format(Birthdate,"mmdd") > Format(#7/31/2004#,"mmdd")

Enter this in the Field: of the query ---

Age: DateDiff("Y",[BIRTHDATE],[Enter date])

Access dropped some functions. I just tried this and it
gave days, not years. If you get days, use this instead -
--

Age: abs((DateDiff("Y",[BIRTHDATE],[Enter date]))/365)
-----Original Message-----
I am trying to develop a query from a database that
calculates the age of a Little League ball player. We
know his existing birthdate and what we need to know is
how old he is in years on a specific date (July 31, 2004).

Thanks

Bob
.
Click to expand...
Click to expand...
 
Enter this in the Field: of the query ---

Age: DateDiff("Y",[BIRTHDATE],[Enter date])

Access dropped some functions. I just tried this and it
gave days, not years. If you get days, use this instead -
Click to expand...

Umm... no. Access didn't drop any functions. You misread the help file
(or couldn't find it more likely, you must open the VBA editor before
you can get help on DateDiff).

"y" is the code for days of the year and is equivalent to "d".
For the number of years use "yyyy".

Also, note that "yyyy" returns the number of whole years - that is the
number of January 1st dates between the birthdate and the desired
date. If you try DateDiff("yyyy", #12/31/2003#, #1/1/2004#) you'll
find that a day-old baby is already a year old! To correct this use

Age: DateDiff("yyyy", [BIRTHDATE], [Enter Date:]) -
IIF(Format([BIRTHDATE], "mmdd") > Format([Enter Date:], "mmdd"), 1, 0)
 
Back
Top