Improve company productivity with a Business Account.Sign Up

x
?
Solved

Calculate age in Access report

Posted on 2016-10-10
11
Medium Priority
?
128 Views
Last Modified: 2016-10-10
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
Comment
Question by:WiddleAvi
  • 6
  • 5
11 Comments
 
LVL 24
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

Open in new window


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

Open in new window

0
 

Author Comment

by:WiddleAvi
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
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

Author Comment

by:WiddleAvi
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
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

by:WiddleAvi
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

by:
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

Open in new window

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

Author Comment

by:WiddleAvi
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
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

by:WiddleAvi
ID: 41837065
It worked !!! Thank you for your patience.
0
 
LVL 24
ID: 41837085
you're welcome ~ happy to help
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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

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: …

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.

Join & Ask a Question