DOB to AGE calculation

[bluenose76]

Hi,

I have looked through the variouse posts on this subject and there are many answers, but i couldnt find the one that suited me? so i am hopeing that someone can point me in the right direction?


What i would like is for when my users enter a date of birth in the format DD/MM/YYYY then my DB automatically calculates the age. is it possible that the DB can reference the system clock so that the age is correct to the day rather than using the year?

if i can get this working correctly then the end result will trigger many other actions that need to be carried out.

any help in this matter is greatly appreciated.

Thank you,

Regards
Bev

 

[KenHigg]

Can you do a datediff() in days and then divide by 365?


ken

 

[Alc]

You can use the Now() function to find the present date and the DateDiff() function to calculate the difference between the two.

 

[RuralGuy]

Here's a function that should work for you:

Public Function Age(DOB As Date) As Integer

Age = DateDiff("yyyy", DOB, Date) + (Date < DateSerial(Year(Date), Month(DOB), Day(DOB)))

End Function

 

[bluenose76]

Guys,

Thank you for you quick responses, Howevere it seems that my knowledge is lacking enough to understand what to do with your information?

Please could you give me that little more direction as to how to actually use your suggestions to get them working?

Thank you again

regards
Bev

 

[Alc]

In the case of what RuralGuy posted, you would call the function and pass it the person's date of birth.

     Dim DOB as Date

     DOB = [I]whatever value you want e.g. the value of some field on a form[/I]

     Call Age(DOB)

You would get the age in return.

 

[RuralGuy]

Hi Bev,
You may need to post what you have so far so we can offer suggestions on how to fix it.

 

[bluenose76]

Rural guy,


all i have at the moment is my one table with a few linked tables and soon to have some queries. the forms and fancy user end stuff will come later

i currently have two fiels side by side,

field one = DOB
field two = Age

it goes without saying that if my end user inputs both items of data then it will not be long before there is a lot of incorrect data.

what i would like is for my user to input the date of birth dd/mm/yyyy and for the age field next to it to automatically work out the age? if this is possible?

i was thinking along the lines of a macro or piece of VB code that runs upon starting the DB to ensure that all are up to date?

Thank you one again for your help

Regards
Bev

 

[RuralGuy]

You should *not* store the age nor have a field for it. Simply calculate the age in a query every time you need it. You can add a field to a query very easily using the Query Builder and then assuming the DOB field is named DOB, set the CurrentAge field to:
CurrentAge:=Age([DOB])
Put the AGE function I posted in a Standard Module. When you run the query, you will see that when the DOB field is completed, the Age field fills in automatically. Post back if you need additional guidance. We may need to modify the function to catch the Nulls.

 

[bluenose76]

Rural Guy,

you are a gentleman, Thank you that works perfectly.

Brilliant, I can now move on to getting the other pieces working the way i would like them to..

Regards
Bev

 

[KenHigg]

A gentleman -

Cut that out -

 

[RuralGuy]

Excellent Bev! Just ignore the guy behind the curtain.