Solved

Calculate age in Access report

Posted on 2016-10-10
11
29 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now