Calculating Age from Birthdate field in Query

[Allie12380]

Hello,

I have a database problem - I'm able to calculate age on my forms and
reports using the DateDiff formula. However, I'd like to be able to
calculate age in my query so that I can copy and paste into Excel easily at
anytime.

Any help on how to do this in a query would be very helpful.

My DOB is formatted as XX/XX/XXXX

I just need the age to be determined in years from DOB to today

Thanks
Monica

 

[vanderghast]

See: http://www.mvps.org/access/datetime/date0001.htm


Vanderghast, Access MVP

 

[Allie12380]

Here is my sql -

SELECT [Mailing List].[List ID], [Mailing List].LastName, [Mailing
List].FirstName, [Mailing List].Address, [Mailing List].Phone, [Mailing
List].City, [Mailing List].State, [Mailing List].[Zip Code], [Mailing
List].Sex, [Mailing List].Birthdate, [Mailing List].[5K/1M], [Mailing
List].[T-shirt size], [Mailing List].[Years run]
FROM [Mailing List]
WHERE ((([Mailing List].[Years run]) Like "containing *5" Or ([Mailing
List].[Years run]) Like "*6" Or ([Mailing List].[Years run]) Like "*7"));

Where/how do I add this to my sql? I'm not sure where I would I insert
either the datediff or the other query language you gave to me?

 

[Allie12380]

Here is my sql code:
SELECT [Mailing List].[List ID], [Mailing List].LastName, [Mailing
List].FirstName, [Mailing List].Address, [Mailing List].Phone, [Mailing
List].City, [Mailing List].State, [Mailing List].[Zip Code], [Mailing
List].Sex, [Mailing List].Birthdate, [Mailing List].[5K/1M], [Mailing
List].[T-shirt size], [Mailing List].[Years run]
FROM [Mailing List]
WHERE ((([Mailing List].[Years run]) Like "containing *5" Or ([Mailing
List].[Years run]) Like "*6" Or ([Mailing List].[Years run]) Like "*7"));

Would I add the DateDiff into my SQL or in design view on the field line? I
was trying to do it in design view to create my new field and perform the
calculation but there was getting the error message that I had too many
characters. If in SQL - where would I add it?

Thanks
Monica

 

[vanderghast]

SELECT [Mailing List].[List ID], [Mailing List].LastName, [Mailing
List].FirstName, [Mailing List].Address, [Mailing List].Phone, [Mailing
List].City, [Mailing List].State, [Mailing List].[Zip Code], [Mailing
List].Sex, [Mailing List].Birthdate, [Mailing List].[5K/1M], [Mailing
List].[T-shirt size], [Mailing List].[Years run]

,
DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") <
Format( [Birthdate], "mmdd") ) AS age

FROM [Mailing List]
WHERE ((([Mailing List].[Years run]) Like "containing *5" Or ([Mailing
List].[Years run]) Like "*6" Or ([Mailing List].[Years run]) Like "*7"));



Vanderghast, Access MVP

Here is my sql code:
SELECT [Mailing List].[List ID], [Mailing List].LastName, [Mailing
List].FirstName, [Mailing List].Address, [Mailing List].Phone, [Mailing
List].City, [Mailing List].State, [Mailing List].[Zip Code], [Mailing
List].Sex, [Mailing List].Birthdate, [Mailing List].[5K/1M], [Mailing
List].[T-shirt size], [Mailing List].[Years run]
FROM [Mailing List]
WHERE ((([Mailing List].[Years run]) Like "containing *5" Or ([Mailing
List].[Years run]) Like "*6" Or ([Mailing List].[Years run]) Like "*7"));

Would I add the DateDiff into my SQL or in design view on the field line?
I
was trying to do it in design view to create my new field and perform the
calculation but there was getting the error message that I had too many
characters. If in SQL - where would I add it?

Thanks
Monica

See: http://www.mvps.org/access/datetime/date0001.htm


Vanderghast, Access MVP