x
Solved

# Calculate age in Access report

Posted on 2016-10-10
Medium Priority
128 Views
I have an Access table that has a persons DOB. I created a report that lists all the entries from that table. I would like to add a field into that report takes the DOB and calculates the persons age to show year and months (We have some children including babies under 1).   Keep in mind that I am an access newbie. Thanks in advance for any help.
0
Question by:WiddleAvi
• 6
• 5

LVL 24

Expert Comment

ID: 41836900
put this function in a general (standard) module:
``````'~~~~~~~~~~~~~~~~~~~~~~~~~~ GetAge
Function GetAge(pDOB As Date _
, Optional pDate As Date = 0) As Integer
' Crystal (strive4peace)
'3-20-09
'get age in years given DOB and the date to take age from
'if pDate is not specified, the current date is used

If pDate = 0 Or Not IsDate(pDate) Then pDate = Date

GetAge = 0
If Nz(pDOB, 0) = 0 Then Exit Function
GetAge = DateDiff("yyyy", pDOB, pDate) _
+ (pDate < DateSerial(Year(pDate), Month(pDOB), Day(pDOB)))
End Function
``````

To call it from, for instance a query, you need to send both the DOB and the optional parameter.

Field --> TheAge: GetAge( [DOB-fieldname], Date() )

queries require that you send optional parameters but in code and other places, like forms, this is not necessary. If Date is not sent, the function will use today's date. It could be written such that sending date is not required. the reason it does have date is to figure out age as of a certain day, like for a tournament or other event.

**********************************************************
*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up
``````Option Explicit ' require variable declaration
``````
0

Author Comment

ID: 41836933
So I copied the code and saved it to a module which I called GetAge

Then in the report under design view I have a field called age and under Data-->Control source I have this code: =GetAge([birthdate],Date())

When I try and run the report a box pops up asking for the parameter of GetAge
0

LVL 24

Expert Comment

ID: 41836942
once the code is in the module sheet, from the menu, do -->
Debug,Compile

and then SAVE -- but do NOT name the module the same as any function or sub -- call it "mod_GetAge" or "bas_GetAge". You can change the Name of the module by showing the properties if it is not already showing -- from the menu: View, Properties window

> "I have a field called age"

If by "field", you really meant "control" then realize that forms and reports contain controls, not fields.
0

Author Comment

ID: 41836961
Ok, So I deleted the module and created a new one with that name. Now when I run the report I am getting this for the age: #Type!

I am attaching a couple of screenshots so you can see what I am doing
Module.jpg
Report.jpg
0

LVL 24

Expert Comment

ID: 41836991
do not store "age" in the table

the equation should be:
=GetAge([birthdate], Date() )

birthdate needs to be stored as date/time data type

be sure do Debug, Compile, and Save the module before you use the function
0

Author Comment

ID: 41836997
Ok, I think it is working but it only shows how old they are in years. I would like months included. At least if the child is under 1 it should show months. I don't need it to shows months for anyone over 1 but if it does it's fine as well.
0

LVL 24

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 2000 total points
ID: 41837030
``````'~~~~~~~~~~~~~~~~~~~~~~~~~~ GetAgeYearsMonths
Function GetAgeYearsMonths(pDOB As Variant _
, Optional pDate As Date _
, Optional piMonthsBeforeYear As Integer = 1 _
) As String
'161010 crystal (strive4peace)
'PARAMETERS
'  pDOB = date of birth
'  pDate = date for calculating age (current date assumed if not specified)
'  piMonthsBeforeYear = age in years to also report months in age

Dim iMonths As Integer _
, iYears As Integer _
, vAge As Variant

'initialize return value
GetAgeYearsMonths = ""
If IsNull(pDOB) Then Exit Function

If pDate = 0 Or Not IsDate(pDate) Then pDate = Date

If Nz(pDOB, 0) = 0 Then Exit Function

vAge = Null

iMonths = DateDiff("m", pDOB, pDate) _
+ (pDate < DateSerial(Year(pDate), Month(pDOB), Day(pDOB)))

iYears = iMonths \ 12
iMonths = iMonths - (iYears * 12)

If iYears >= 1 Then
vAge = iYears & " yr"
End If

If iYears <= piMonthsBeforeYear Then
If iMonths > 0 Then
vAge = (vAge + ", ") & iMonths & " mo"
End If
End If
if isnull(vage) then vage="0"
GetAgeYearsMonths = vAge

End Function
``````
notice this function needs 3 parameters, which are explained at the top in a comment
0

Author Comment

ID: 41837045
I am a bit confused. What is piMonthsBeforeYear = age in years to STOP also reporting months in age ?
That is the 3rd Variable, correct ? What should I be sending for that ?
0

LVL 24

Expert Comment

ID: 41837055
if you only want to see months for less than age 2, use 1. If you only want to see months for first year, use 0. Sorry I didn't explain that very well ~

Note that now the function returns a string, not a number
0

Author Closing Comment

ID: 41837065
It worked !!! Thank you for your patience.
0

LVL 24

Expert Comment

ID: 41837085
you're welcome ~ happy to help
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
###### Suggested Courses
Course of the Month9 days, 12 hours left to enroll

#### 606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.