Calculating Birthdates

G

Guest

i have 1 table with app. 60 000 members & have to calculate all their ages. I
have the D.O.B of all in 1 field. I have followed the assistance given on
your site under "Calculating age from birth date in Access" but this only
allows me to calculate 1 member's age @ a time. & i don't have success with
this either as the last step does not work out

Pls advise urgently!!!
 
G

Graham R Seach

There is more than one person here. Whose site are you talking about?

I get the feeling you're trying to do this in a query. If so, then create
the query, and add the following to the Field row in a spare column:
Age:
Abs(DateDiff("yyyy",DOB,Date())+(Format(Date(),"mmdd")<Format(DOB,"mmdd")))

If your date of birth field is named something other than DOB, change the
above code to suit.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
G

Guest

Graham,
Thx a mil for the great response. Answer 2 ur 1st question, i went onto
Microsoft site.
Could i ask u pls to give me an e.g. of the formula below as i seem to be
doing it wrong. Say the DOB is 22/05/1971 & i am in the year 2005.
Thx for ur patience.
mylove
 
G

Graham R Seach

I just gave you the formula.

For a query:
Age:
Abs(DateDiff("yyyy",DOB,Date())+(Format(Date(),"mmdd")<Format(DOB,"mmdd")))

For VBA:
intMyAge =
Abs(DateDiff("yyyy",DOB,Date())+(Format(Date(),"mmdd")<Format(DOB,"mmdd")))

....by the way, the above formula says you're 32 as of today's date.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
G

Guest

yes, i appreciate the formulat, but my confusion comes in when do i insert
today's date & when do i insert, etc
 
G

Graham R Seach

You don't need to insert *today's* date - it's already there in the form of
the Date() function.

However, if you mean how do you insert the date of birth, then just add it
manually:
Age = Abs(DateDiff("yyyy", DateSerial(1956, 11, 5), Date) + _
(Format(Date, "mmdd") < Format(DateSerial(1956, 11, 5),
"mmdd")))

....or wrap the formula in a function:
Public Function Age(DOB As Date) As Integer
Age = Abs(DateDiff("yyyy", DOB, Date) + _
(Format(Date, "mmdd") < Format(DOB, "mmdd")))
End Function

Then call the function like so:
MsgBox "I am " & Age(DateSerial(1956, 11, 5)) & " years old."
...or...
MsgBox "I am " & Age(dteMyDOB) & " years old."

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top