calc date of birth in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

a - 13 Jun 2007 15:21 GMT

I have query contain 2 fields date of birth and today date and on calculated
field name calcdateofbirth
I want to know the the date of birth of any body by year and month
i can use this expression to calc the date of birth by year only like this
expression:
calcdateofbirth: DateDiff("yyyy";[dateofbirth];[todaydate])
I want to calcdateofbith by year and month in query
thank you

Reply to this Message

Marshall Barton - 13 Jun 2007 16:30 GMT

>I have query contain 2 fields date of birth and today date and on calculated
>field name calcdateofbirth
[quoted text clipped - 3 lines]
>calcdateofbirth: DateDiff("yyyy";[dateofbirth];[todaydate])
>I want to calcdateofbith by year and month in query

will this help?

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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Klatuu - 13 Jun 2007 16:46 GMT

This would do it for you.

SELECT cdclt.CLDOB,
DateDiff("yyyy",[CLDOB],Date())+IIf(Month(Date())<Month([CLDOB]),-1,IIf(Month(Date())=Month([CLDOB]) And Day(Date())<Day([CLDOB]),-1,0)) AS Age
FROM cdclt;

Signature

Dave Hargis, Microsoft Access MVP

> I have query contain 2 fields date of birth and today date and on calculated
> field name calcdateofbirth
[quoted text clipped - 4 lines]
> I want to calcdateofbith by year and month in query
> thank you

Reply to this Message

Douglas J. Steele - 13 Jun 2007 18:07 GMT

Marsh and Dave have both shown you have to correctly calculate the age in
years. However, you mention you want year and month. Check
http://www.accessmvp.com/djsteele/Diff2Dates.html for "A More Complete
DateDiff Function"

Signature

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

>I have query contain 2 fields date of birth and today date and on
>calculated field name calcdateofbirth
[quoted text clipped - 4 lines]
> I want to calcdateofbith by year and month in query
> thank you

Reply to this Message