Solved

Calculate age in Access report

Posted on 2016-10-10
11
44 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 19
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 19
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 19
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 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 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 19
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 19
ID: 41837085
you're welcome ~ happy to help
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

821 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