using access to calculate age from DOB (1 Viewer)

[mat.creativeroom]

Hi I'm trying to find a way to automate a calculation of someones Age from inputting their Date of Birth that will automatically update each time you open access.

Could someone please provide me with a walkthrough? or at least give me some pointers because I'm completely lost!

 

[Mike375]

Try this.

If in an unbound text box

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

If used to create a new field in a query

NewFieldname: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

 

[mat.creativeroom]

Hi Mike,

Thanks for this but could you break it down a bit further, also to add to this I want to input a date of birth into one field and output the calculated age into another field.

I have not done any programming in Access before at all so if you could break it down into chunk by chunk steps it would be massively helpful. Or if someone else could give me a hand.

Cheers

 

[raskew]

Hi -

"...inputting their Date of Birth..." is a recipe for disaster since it's an open invitation for typos. Since DOB remains constant, it should be stored as a field. Then, create a calculated field as Mike described, i.e.:

NewFieldname: DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd"))

Note that I've replaced Now() with Date() since Time (as returned by Now()) plays no part in an Age calculation.

DateDiff("yyyy",[DOB],Date()) subtracts one year from another, without regard to whether a full year has past, e.g. DateDiff("yyyy", #12/31/07#, #1/1/08#) will return 1, when in fact only one day has past.

Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")) is a Boolean statement which will return -1 if True, 0 if False.

HTH - Bob

 

[Mike375]

Hi Mike,

Thanks for this but could you break it down a bit further, also to add to this I want to input a date of birth into one field and output the calculated age into another field.
Cheers

That is exactly what a calculated field in a query does.

I suspect you are not familiar with making queries or fields on a form. If you don't have any books on Access then use Access Help (Or Google) and search on Query and unbound textbox or unbound controls.

Using Bob's more correct version

NewFieldname: DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd"))

You enter the above on the Field row of the Query Grid (what you see for a new query to be made or opening an existing query in design view)

[DOB] is replaced with the field name you use where you have date of birth entered. NewFieldName is whatever you want to call the new field that will display age.

If your table had 5 fields (including your date of birth field) and you made a Select query that had all 5 of the table field and you added the NewFieldname then when you opened the query it would look just like your table except there would now be 6 fields and you would see the persons age on every record.

If it is done for an unbound text box then this is on a form. An unbound textbox is like a field on a form but unlike a field it does not display data from the underlying table/query. In this case it would display the results of the formula and so you would see the persons age on the form.

With something like Age it is best not to actually store the figure and one big reason being that age changes. When Age is done as a calculation then the number will automatically change as the person has a birthday.

If for some reason you wanted to store the age value then you would add a field to your table and do an Update Query. An Update query would basically do what copy and paste would do, that is, place all the ages in the calculated field into the new field in the table.

 

[charlie_n]

Hi, I am trying to use this same formula, but would like to have everyone's age calculated from 1/Jan/20xx. Ideally this would automatically be calculated from the current year, with the day and month hardcoded.
I have tried doing something like "1/1/year()"
Another option I thought of would be to reference another table, and just have a single field with the date which can be updated by the admin if the as at date ever changes.
Is this going to be possible?
Thanks

 

[Brianwarnock]

To get the 1st jan in the current year use Dateserial

Dateserial(Year(Date()),1,1)

Brian

 

[StuartP]

Hi Guys,

I came across this code the other Day which I actually modified to calculate the Age of an Insurance policy but here is the original code I found.

Age() Function
Given a person's date-of-birth, how do you calculate their age? These examples do not work reliably:
Format(Date() - DOB, "yyyy")
DateDiff("y", DOB, Date)
Int(DateDiff("d", DOB, Date)/365.25)
DateDiff("y", ..., ...) merely subtracts the year parts of the dates, without reference to the month or day. This means we need to subtract one if the person has not has their birthday this year. The following expression returns True if the person has not had their birthday this year:
DateSerial(Year(Date), Month(DOB), Day(DOB)) > Date
True equates to -1, so by adding this expression, Access subtracts one if the birthday hasn't occurred.
The function is therefore:

​

Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if missing.
'Return: Whole number of years.
Dim dtDOB As Date
Dim dtAsOf As Date
Dim dtBDay As Date 'Birthday in the year of calculation.

Age = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtDOB = varDOB

If Not IsDate(varAsOf) Then 'Date to calculate age from.
dtAsOf = Date
Else
dtAsOf = varAsOf
End If

If dtAsOf >= dtDOB Then 'Calculate only if it's after person was born.
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End If
End If
End Function

​

 

[Stefan53nz]

Hi This thread appears to be very close to what I need & Brian's suggestion of using "Dateserial(Year(Date()),1,1)" I think is the answer I need but am unsure where to insert it or what would need to be replaced in the code I have.

If someone could advise I would be grateful.

The code I have is

'----------------------------Code Start--------------------------------
Public Function AgeGroup(dtmBirthDate As Date) As String

Dim intAge As Integer

'Age Calculation
intAge = DateDiff("yyyy", [dtmBirthDate], Now()) + _
Int(Format(Now(), "yyyy") < Format([dtmBirthDate], "yyyy"))

Select Case intAge

'For each Age range, write out Age Group (used in qry)
Case 0 To 10
AgeGroup = "Kiwi"
Case 11 To 13
AgeGroup = "Cub"
Case 14 To 15
AgeGroup = "Intermediate"
Case 16 To 17
AgeGroup = "Cadet"
Case 18 To 20
AgeGroup = "Junior"
Case Is > 20
AgeGroup = "Senior"
End Select

End Function
'----------------------------Code End-----------------------------------

I need to have age groups set as at 1st January.

Thanks
Stefan

 

[RainLover]

Just to add to the confusion see attached sample.

 

[Luka2013]

I used this one, it makes more sense to me. Please excuse the naming of my variables.
I created a database that allocates learners to their different sport codes. So I have a Combo box to select the different sports like(Volleyball and basketball). But I wanted when I select a sport on the combobox, it would immediately compare todays date with the learners birth date which automatically appears because I created a relationship between learners table and the sports table in an allocation table. Then as soon as the comparison is done, then the Age_Group Field will store U/15 or U/17 or U/19 depending on their age.
I tried my best to explain below. But please don't hesitate to comment if you need further help.
Private Sub Combo4_Click()
Dim x As Integer
Dim era As Integer
Dim start As Integer
Dim final As Integer
Dim count As Integer

start = DatePart("yyyy", Date) 'to store the cureent year eg.2015

final = DatePart("yyyy", Me.Birth_Date) ' to store the year of the date of birth eg.1999

For count = start To final 'to count from 1999 to 2015

If count Mod 4 = 0 Then 'checks each year whether it is a multiple of four(Leap year)
era = era + 1 'to count the multiples of four, if a year is a multipleof four then it is a leap year
End If

Next

x = Date - Birth_Date 'to determine the number of days between todays date and the date of birth

x = x - 4 'to subtract the extra days that where added when it was a leap year (366+365+365=1096)
'there is one leap year so we will subtract one 1096-1 = (365+365+365)

x = x / 365 'now divide the anumber of days between todays date and the Date of birth by 365 to get the number of years

If x > 17 And x <= 19 Then 'determines the age group between 17 and 19
Age_Group = "U / 19" 'if the value is truely between 17 and 19 then the Textbox Age_Group will store this value

ElseIf x > 15 And x <= 17 Then 'determines the age group between 15 and 17
Age_Group = "U / 17" 'if the value is truely between 15 and 17 then the Textbox Age_Group will store this value

ElseIf x >= 11 And x <= 15 Then 'determines the age group between 11 and 15
Age_Group = "U/13"
Else
Dis.Visible = True

Dis.Caption = "This learner is not old enough or is to old to fall under any of the Age groups"
'this will be the error message displayed if non of the conditions are met , it iwll be displayed in a label called Dis
End If

END SUB