Expression for age on specific date

[manfbks]

Using Access 2003, Windows XP
I have a birthdate field and need age as of 8/10/2007.

Using: =DateDiff("yyyy",[birthdate],"8/10/2007")

Not getting correct age for birthdates after the 10th of August in any year.

Seems simple, but haven't gotten it right in all our tries.
Mary Ann

 

[Joe4]

It works for me, but maybe you have different default date format (but be sure that "birthdate" is in a date format).

In any event, try this:
Expr1: DateDiff("yyyy",[MyDate],DateSerial(2007,8,10))

 

[manfbks]

jm14, need more

Thanks for your reply, jm14, but your expression gives me the same result.

I have a "birthdate" field and need age as of August 10, 2007. If the birthdate is 8/9/1927 the age is 80 on 8/10/2007. If the birthdate is 8/10 or 8/11/2007 the age needs to be 79 because she hasn't had her birthday yet this year. I am getting 80 in all three cases and all birthdates after August 9th with my expression or your expression.

I am new to Access and don't have new ideas.

 

[Marbles]

This might help:

http://www.mrexcel.com/board2/viewtopic.php?t=234458&highlight=birthdate

 

[manfbks]

Need more help with age query

Thank you Marbles. The expression from the Extracting Age topic board from Andrew Fergus works in the query I have created.

I have a Participants table, and a form and reports. I need to get the form and reports to use the age query. Can I get an Age column in the Participants table to use the query?

I can put the expression into the Age box on the form, but I don't know how to use the query there. My report was designed to use the typed number in the Age column in the Participants table, until I decided I wanted to use an expression instead. I know this is backwards, but can I modify to get what I want?

I'm sorry, I am so inexperienced that I don't even know if my question makes sense to anyone else.

Mary Ann

 

[Joe4]

You never want any calculations or calculated field in your base table. To keep things dynamic and to maintain database integrity, calculations like this are best done in a query.

The great thing is that any Form or Report can use the query as its Control Source instead of the table. So if you want these calculations to be available in your Form/Report simply change the Control Source to use your new query.