Access for dummies - Calculating current age (1 Viewer)

[PS-Lee]

Hi there.

Let me start by saying I'm very very new to Acess and coding-language and terms, so I am prone to asking stupid questions and require clarifications, mainly because I want to learn.

I'm setting up a resume database for my employer and they have asked me to calculate applicants actual ages rather than their date of birth. I've tried getting it to work with calculation field that draws on today's date and their date of birth, but today's date doesn't update. I've dabbled with an afterUpdate event to update the current date, to no avail.

Now, from browsing this forum, I can see that the best way to do this calculation is in a query, but I have had no luck actually getting this to work with the different codes suggested (DateDiff with Int, Date() and Now() ). I wish I could clarify exactly what the issue is, but I have no idea at all.

Any suggestions for an Access Dummy?

 

[arnelgp]

you get Today's date by using Date() function.
here is a function, you copy in Standard Module:

create a Query to call the function:

select applicantTable.ID, applicantTable.Name, applicantTable.DOB, fnAge([DOB], Date()) As Age
from applicantTable;

Function fnAge(dtmBD As Date, Optional dtmDate As Date = 1) _
 As Integer
    ' Calculate a person's age, given the person's birth date and
    ' an optional "current" date.
    If dtmDate = 1 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    fnAge = DateDiff("yyyy", dtmBD, dtmDate) + _
     (dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
      Day(dtmBD)))
End Function

 

[PS-Lee]

select applicantTable.ID, applicantTable.Name, applicantTable.DOB, fnAge([DOB], Date()) As Age
from applicantTable;

Thank you for the reply. This part confuses me a bit. I'm not sure what exactly it is I'm meant to do do here.

Function fnAge(dtmBD As Date, Optional dtmDate As Date = 1) _
As Integer
    ' Calculate a person's age, given the person's birth date and
    ' an optional "current" date.
    If dtmDate = 1 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    fnAge = DateDiff("yyyy", dtmBD, dtmDate) + _
     (dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
      Day(dtmBD)))
End Function

Is there anything I need to change to the names of the actual tables and fields in my database within the code?

Spoiler

I said I was a dummy

 

[arnelgp]

see this demo.
the function i made is in Module1 (press Alt-F11 to goto VBA).
there is a datasheet form that will open.
the form record comes from Query1.
see Query1 in design view.
enter some data.

 

[PS-Lee]

see this demo.
the function i made is in Module1 (press Alt-F11 to goto VBA).
there is a datasheet form that will open.
the form record comes from Query1.
see Query1 in design view.
enter some data.

Thank you so much for the demo. I'll have to test out the module to see it I can can get it to work (the demo works with no issues)

 

[moke123]

Here's another similiar to Arnels

Public Function AgeYears(ByVal datBirthDate As Date) As Integer
  ' Comments: Returns the age in years
  ' Params  : datBirthDate    Date to check
  ' Returns : Number of years
  ' Source  : Total Visual SourceBook
 
  On Error GoTo Proc_Err
 
  Dim intYears As Integer
 
  intYears = Year(Now) - Year(datBirthDate)
 
  If DateSerial(Year(Now), Month(datBirthDate), Day(datBirthDate)) > Now Then
   ' Subtract a year if birthday hasn't arrived this year
    intYears = intYears - 1
  End If
 
  AgeYears = intYears
 
Proc_Exit:
  Exit Function
 
Proc_Err:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
  Resume Proc_Exit
End Function

 

[mike60smart]

You can also do it like this in an Unbound Textbox on your Form

 

[MarkK]

Can't you just format the difference between two dates? Like...

Function GetAge(Birth As Date) As Integer
    GetAge = Format(Date - Birth, "yy")
End Function

...and Format() is available in SQL, so maybe...

Age: Format(Date() - tYourTable.Birth, 'yy')

Mark

 

[jdraw]

Here's an Age function that may be useful

'---------------------------------------------------------------------------------------
' Procedure : Age
' Author    : Jack (from awf)
' Date      : 06-09-2012
' Purpose   : This routine determines the Age of a Person given their DOB.
' It accounts for the birthday this year (whether passed or not). A second parameter
' Specdate allows you to work from a different Date than today's date.
'If SpecDate is missing, the routine defaults to today's date.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Public Function Age(dteDOB As Date, Optional SpecDate As Variant) As Integer
          Dim dteBase As Date, intCurrent As Date, intEstAge As Integer
10    On Error GoTo Age_Error

20    If IsMissing(SpecDate) Then
30      dteBase = Date
40    Else
50      dteBase = SpecDate
60    End If
70    intEstAge = DateDiff("yyyy", dteDOB, dteBase)
80    intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
90    Age = intEstAge + (dteBase < intCurrent)

100   On Error GoTo 0
110   Exit Function

Age_Error:

120   MsgBox "Error " & Err.Number & "  in line " & Erl & " (" & Err.Description & ") in procedure Age of Module AWF_Related"
End Function

 

[The_Doc_Man]

Let me start by saying I'm very very new to Acess and coding-language and terms, so I am prone to asking stupid questions and require clarifications, mainly because I want to learn.

In this context, there is no such thing as a stupid question.

Just to clarify, I read the article and the replies, but perhaps an explanation HERE might help you a LOT MORE elsewhere. You ALWAYS want the data of birth as a starting point to compute age. Don't compute DOB. Compute AGE, which you can do fairly easily. Here is the thing you need to know (and take away) from the discussion. If you have a fixed reference point, it is easy to compute distance (or fixed reference time and compute age). But if you don't need to KEEP age or distance, you just compute them as needed. The odds are that most of the time you will not need to compute such things. But always remember, what you keep does not have to be what you display because Access is able to compute that sort of thing "on-the-fly" with ease.

Therefore, keep the fixed reference, which (because it is fixed) needs no maintenance. Then compute the relative information when needed. And the basic principle is that for Access, it is often practical to differentiate between a stored value and a displayed value.

Why, you ask, do you keep on recomputing something for display? Answer, well for one thing, age isn't constant - but Date of Birth IS. Does this help you understand WHY you are getting the advice you are getting?