Working out ages!

[Hiker]

I was wondering if anybody knows what criteria I need, if I wish to work out how old somebody is now (current date) from knowing their date of birth!

Thanks!

 

[jstroh]

Use the following code and you will get how old the person is in years.


=DateDiff("yyyy",[Birthdate],Now())

Birthdate is the field name you have assigned to the individuals Birthday.

 

[Hiker]

Thanks jstroh!


Just hope that this thing will work!!

 

[Hiker]

AHHHHHHHH!!

That criteria does not seem to work on my computer!! Maybe it is something to do with my field name, am I aloud to have an input mask on it, and what data type should I be using? I have got text, or is there something else I am doing wrong?
Please Help!!

 

[Jack Cowley]

The Birthdate field in you table should be a Date/Time field...

 

[Hiker]

I've had that and set it as short date in the input mask and it has the format of dd/mm/yyyy but somehow it still does not want to work in the query, maybe I am doing something wrong with the criteria?

Thanks for the Help!

 

[varunmathur]

Hi!
The datediff does'nt work very reliably in my database so I simply use :-
([end date]-[start date])/365
and set the result to zero decimals.
It works OK
varun

 

[Pat Hartman]

Try this article from the knowledge base.

Q100136 - ACC Two Functions to Calculate Age in Months and Years

 

[Hiker]

Thanks for the tip!!!

but I was wondering where do I find this article?

Thanks again!

 

[DJN]

Hi Hiker,

Just follow the link and click on the 'Specific ID Number' radio button. Type in Q100136 and that should take you to the article. http://search.support.microsoft.com/kb/c.asp?

 

[Hiker]

Hi,

Well I found the code and I did put it in a module-but I don't know how to make it work in access afterwards?

I tried the enddate startdate expression, but somehow I do not quite understand how that works-where does it give you the answer?
And is there a way on that so, that you can use a date that you have got allready?

If i do want to use the DateDiff criteria are there any perticular things that I need to set up before this will work?

OR is there any alternative - like an update query of somesort or setting parameters to work out ages????????????????

If you have got ANY suggestions please do tell me

Thanks

 

[Travis]

In a Query you can resolve an age using a query similar to this:

SELECT [Name], [BDAY], DateDiff("yyyy",[bday],Date())-IIf(CDate(Month([bday]) & "/" & Day([bday]))>Date(),1,0) AS [Curr Age]
FROM tblBirthday;


Notice that I have added code to subtract 1 year if the Birthday has not happened yet this year, using the IIF statement.

or you can create a public function and use it in the query:

'Code for a module
Public Function YearsOld(ByVal dBDAY As Variant) As Integer
dBDAY = CDate(dBDAY)
YearsOld = DateDiff("yyyy", dBDAY, Date)
If CDate(Month(dBDAY) & "/" & Day(dBDAY)) > Date Then
YearsOld = YearsOld - 1
End If

End Function

'Query to be used with code:
SELECT [Name], [BDAY], YearsOld([bday]) AS [Curr Age]
FROM tblBirthday;

 

[Hiker]

Thanks for the help!

I don't really understand Visual Basic Code, I understand how to put it into a module but I have no idea how to run it afterwards, so I can use that code.

So I need a little help.

 

[Travis]

Place the code in a module.

If you look at the SQL Statement I gave you you will see the "YearsOld" function already there. This is how you can call a function from inside a Query.

 

[Hiker]

I have got it into the query ok, but when I try and run it, it comes up with "run time error 13 type mismatch"

If I say yes to debug it highlights this line for me in the code:

dBDAY = CDate(dBDAY)

I don't know how to fix the problem though.
please help

Thanks

 

[Hiker]

AHhh!

running out of time and needing help!!!

 

[Travis]

What does your Query Look Like? (Paste your SQL Statement)

What does the Data in the Field Look Like?

 

[Surjer]

Is the format of the field "bday" in you're table set to date/time?