Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate age in Access report

Posted on 2016-10-10
11
Medium Priority
?
91 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 22
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 22
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 your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

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 22
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 22
ID: 41837085
you're welcome ~ happy to help
0

Featured Post

[Webinar] Protection from Cyberattacks

In this session, we’ll dive into the complexities of modern cyber threats and why only multi-vector protection can keep today’s businesses secure through the various stages of a cyberattack, across multiple vectors. Thursday September 14, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

730 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