Avatar of TechGuise
TechGuise
Flag for United States of America asked on

Arriving at an age in years based on birthday and a specified day.

Trying to calculate the age of a person (in years) on a particular date.

I have the following fields in the table

DOB = "Date of Birth", date field

EOGM = "End of Game Year"


So if the end of the game year is 8/31/2022, and someone was born 9/2/1972, then they shouldn't be considered 50 years old.   To be 50 or older, they should have been born on 8/31/1972 or earlier.   Right?


I've been trying to use DateDiff, combined with DateSerial to get an age in years, but no luck.  I've also tried Int()


Feel like I'm close....   just can't get what I think is the right answer.

* Access 2019VBA

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

This "Age" function I found on a Microsoft site (and modified to include your EOGM rather than Now) says your person is 50.

Function Age(varBirthDate As Variant) As Integer
 Dim varAge As Variant
 
 If IsNull(varBirthDate) Then Age = 0: Exit Function
 
 varAge = DateDiff("yyyy", varBirthDate, CDate("8/31/2022"))
 If Date < DateSerial(Year(Now), Month(varBirthDate), _
 Day(varBirthDate)) Then
 varAge = varAge - 1
 End If
 Age = CInt(varAge)
End Function

Sub test()
MsgBox Age("9/2/1972")
End Sub

Open in new window

Martin Liss

I corrected my change. It now gives 49.

Function Age(varBirthDate As Variant) As Integer
Dim varAge As Variant

If IsNull(varBirthDate) Then
    Age = 0
    Exit Function
End If

varAge = DateDiff("yyyy", varBirthDate, CDate("8/31/2022"))
If CDate("8/31/2022") < DateSerial(Year(CDate("8/31/2022")), Month(varBirthDate), Day(varBirthDate)) Then
    varAge = varAge - 1
End If
Age = CInt(varAge)

End Function

Open in new window

Fabrice Lambert

Hmmmm,

If the fonction recieve a null value, it should return null since it can't perform its job (computing an age from null make no sens).
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
TechGuise

ASKER
I feel like Martin's AGE function will work (don't think the NULL possibility will be a problem)

Probably a silly question.....  but do I put this AGE FUNCTION in a "global" module, then I would be able to refer to it in an update sql statement (that is run from a button on a form)?

I need a process to run thru the CONTACTS table and UPDATE the AGE field based on results.

Originally I had a YES/NO field for [50AndOver], but after seeing what was most likely possible with DateDiff, i created a new field called AGE, and will just select the "50 and over" folks from that.
THanks for your help
Martin Liss

If you change the function's declaration statement to the following it will be available everywhere in your workbook.
Public Function Age(varBirthDate As Variant) As Integer
Martin Liss

I changed the function to include a second parameter for the EOGM date which I assume is variable.
Public Function Age(dteBirthDate As Date, dteEOGM As Date) As Integer
Dim intAge As Integer

If IsNull(dteBirthDate) Then
    Age = 0
    Exit Function
End If

intAge = DateDiff("yyyy", dteBirthDate, dteEOGM)
If dteEOGM < DateSerial(Year(dteEOGM), Month(dteBirthDate), Day(dteBirthDate)) Then
    intAge = intAge - 1
End If
Age = intAge

End Function
Sub HowToUse()
MsgBox Age("9/2/1972", "8/31/2022")
End Sub

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Fabrice Lambert

If you want to call the function from a query, it must match the following requirement:
- Must be in a standard module.
- Must be public.
- Arguments (if any) must be variant (ehence they must be tested against Null value).
- Return data type must be variant, to supportNull value.
- Must apply database math.

Database math follow the rules below:
Any concatenation performed with a Null value yeld the value itself converted into string if necessary (concatenation only apply to strings), the convertion can be implicit:
"my string" & Null = "my string"
345 & Null = "345"

Open in new window

Any operation performed with a Null operand yeld Null:
1248 * Null = Null
256 And Null = Null

Open in new window

Any comparition performed against a Null value is False:
If(5 = Null)       '// the test is False
If(Null = Null)    '// The test is false

Open in new window


If you don't intend to call the function from a query, arguments can be strong data type (heavilly recommened), in such case, there is no need to test for null arguments since strong data types do not support Null values.
Fabrice Lambert

So, I allow myself to fix Martin's 2nd code:
Public Function Age(dteBirthDate As Date, dteEOGM As Date) As Integer
    Dim intAge As Integer
        '// arguments are Date type, wich is strong.
        '// Testing against null is a no sens.
    intAge = DateDiff("yyyy", dteBirthDate, dteEOGM)
    If dteEOGM < DateSerial(Year(dteEOGM), Month(dteBirthDate), Day(dteBirthDate)) Then
        intAge = intAge - 1
    End If
    Age = intAge
End Function

Open in new window

TechGuise

ASKER
So, here is what I'm trying.
I changed a couple of names to make better sense (for project)....  
Getting an error when it tries to get the contact date of birth (DOB).  

dteEORY... (instead of EOGY  This parameter will never be blank.
Private Sub Cmd4_Click()
Dim dteDOB As Date
Dim dteEORY As Date
Dim intAge As Integer
dteEORY = DLookup("FieldValue", "tblParameters", "[FieldName] = 'CurrentYearEnds'")
Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset
Set MyRst = CurrentDb.OpenRecordset("tblContacts")

MyRst.MoveFirst
Do Until MyRst.EOF
MyRst.Edit
dteDOB = MyRst![DOB]
    If IsNull(dteDOB) Then
    MyRst![Age] = 0
    MyRst.Update
    MyRst.MoveNext
        GoTo NoDOB
    End If

intAge = DateDiff("yyyy", dteDOB, dteEORY)
If dteEORY < DateSerial(Year(dteEORY), Month(dteDOB), Day(dteDOB)) Then
    intAge = intAge - 1
End If
MyRst![AgeAtEOY] = intAge
NoDOB:
    MyRst.MoveNext
    
Loop
       
MyRst.Close
Set MyRst = Nothing

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
TechGuise

ASKER
Just tested with fewer records, and it did not error out if the DOB isn't blank.  

But it also did not update the Age At End Of Year field (AgeAtEOY)

So what do I need on the line to skip around a NULL birthday?
and what am I missing to update the field for their age?

Thanks so much
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
TechGuise

ASKER
Good catch, I had left the MyRst.Update line off after setting the AGE.
CHecking for If Not IsNull, also seemed to do the trick instead of the way I was trying.  

Thanks for taking time to deal with my less than perfect method.   I realize it isn't textbook, but we're not transplanting kidneys or anything.  :o)
Martin Liss

You’re welcome and I’m glad I was able to help. Thank you for the testimonial.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Distinguished Expert in Excel 2018
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
              Experts Exchange Top Expert VBA 2018 to 2020
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.