Age Field Calculated from DOB to current Date (1 Viewer)

TJBernard

Registered User.
Local time
Today, 13:37
Joined
Mar 28, 2002
Messages
176
I have a Field to store the Age of an individual. The user wants this Age Field to autocalculate from a Date Of Birth Field also stored in the table. I was trying to think of a way to subtract the DOB field from the Date() function as the Default value of the Age Field. I could not find a way to do this. Am I on the right track? Is this even possible or feasible? Let me know if anyone has tried this before.

Thank you in advance,

T.J.
 

Cosmos75

Registered User.
Local time
Today, 08:37
Joined
Apr 22, 2002
Messages
1,281
DateDiff

From Access Help file


"The next example shows how to use the DateDiff function in a query expression. Suppose you have an Orders table that contains an OrderDate field and a ShippedDate field. You can create a calculated field in a query to display the time elapsed between an order date and a shipped date for each order. In the Query window, create a new query by adding the Orders table and dragging the OrderID field to the query design grid. In an empty Field cell, enter the following to create a calculated field.

DaysElapsed: DateDiff("y", [OrderDate], [ShippedDate])"


You can change "y" to "ww" for week, and "mm" or is it "m" for month
 
Last edited:
R

Rich

Guest
Search here for DOB etc., there have been many examples posted
 

raskew

AWF VIP
Local time
Today, 08:37
Joined
Jun 2, 2001
Messages
2,734
Gotta read the fine-print. The OrderDate/ShippedDate example is straight out of the Access Help File. Had the responder bothered to test, he/she would have found that (from the debug window):

OrderDate = #8/27/00#
ShippedDate = #8/25/02#
DaysElapsed = DateDiff("y", OrderDate, ShippedDate)
? DaysElapsed
728

Accurate, but not too helpful when the intent is to determine the persons age (presumably in years).

You could, however, modify it as follows:
AgeInYrs = int(DateDiff("y", OrderDate, ShippedDate)/365.25)
? AgeInYrs
1

It would seem that one could use:
AgeInYrs = DateDiff("yyyy", OrderDate, ShippedDate)
…but, when you try, it comes up with the wrong answer.

OrderDate = #8/27/00#
ShippedDate = #8/25/02#
AgeInYrs = DateDiff("yyyy", OrderDate, ShippedDate)
? AgeInYrs
2

The AgeInYrs should be 1.
 
R

Rich

Guest
You missed a couple of points Bob, if someone is under 1, they will be shown as 0, using your method if someone's birthday is today their age will not increase untill tomorrow, the Day function should be included and the original poster wanted the statement as the default value for a field indicating storing the age, bad move surely?
 

Cosmos75

Registered User.
Local time
Today, 08:37
Joined
Apr 22, 2002
Messages
1,281
My bad!

Sorry, I've personally never used Datediff, just wanted to give TJBernard a starting point in case no one replied.

SORRY, TJBernard!

- just trying to help!
 

TJBernard

Registered User.
Local time
Today, 13:37
Joined
Mar 28, 2002
Messages
176
Thank you so very much for all your help, it is greatly appreciated by me and I am sure I will get it to work, I just need some ideas. I never considered DateDiff to start with, so this will send me in the correct direction.

Thank you again,

T.J.
 

thouston

Registered User.
Local time
Today, 13:37
Joined
Aug 6, 2002
Messages
44
If you want a real smart-alec solution to this, displaying age in years/days, try the following: Define a field "age" in your query:

age: IIf(Year(Date())/4=Round(Year(Date())/4,0),
Int((Date()-[DOB])/366) & "yr " & Int(((Date()-[DOB])/366-Int((Date()-[DOB])/366))*366) & "days",
Int((Date()-[DOB])/365) & "yr " & Int(((Date()-[DOB])/365-Int((Date()-[DOB])/365))*365) & "days")

The Iif statement gets around the 365.25 fudge, and then you can get years and days. I tried to be even cleverer and do one with years, months and days but that really gets hairy. Anybody with too much time on their hands might like to try it...
 

raskew

AWF VIP
Local time
Today, 08:37
Joined
Jun 2, 2001
Messages
2,734
Sometimes necessity forces up to come up with hairy solutions. This one emulates the method the military and other agencies use to calculate time in service, etc..

Code:
Function AgeCount(varDOB As Variant, varDate As Variant) As String
'
' PURPOSE: Determines the difference between two dates.
'
' ARGUMENTS:  (will accept either dates (e.g., #03/24/00#) or
'              strings (e.g., "03/24/00")

'  varDOB: The earlier of two dates.
'  varDate: The later of two dates.
'
' RETURNS:  A string as years.months.days, e.g., (17.6.21)
'
' NOTES: To test:  Type '? agecount("03/04/83", "03/23/00")
'                  in the debug window. The function will
'                  return "17.0.19".

Dim dteDOB As Date, dteDate As Date, intoldyears As Integer
Dim intoldMonths As Integer, intoldDays As Integer
Dim intnuyears As Integer, intnumonths As Integer, intnudays As Integer
Dim intyears As Integer, intmonths As Integer, intdays As Integer

Dim agehold As String

If Not IsNull(varDOB) And Not IsNull(varDate) Then
dteDOB = DateValue(varDOB)
dteDate = DateValue(varDate)

intoldyears = Year(dteDOB)
intoldMonths = Month(dteDOB)
intoldDays = Day(dteDOB)
intnuyears = Year(dteDate)
intnumonths = Month(dteDate)
intnudays = Day(dteDate)

If intnudays < intoldDays Then
   intnudays = intnudays + 30
   intnumonths = intnumonths - 1
End If
If intnumonths < intoldMonths Then
   intnumonths = intnumonths + 12
   intnuyears = intnuyears - 1
End If

intyears = intnuyears - intoldyears
intmonths = intnumonths - intoldMonths
intdays = intnudays - intoldDays
agehold = LTrim(Str(intyears)) & "." & LTrim(Str(intmonths)) & "." & LTrim(Str(intdays))
AgeCount = agehold
End If

End Function
 

thouston

Registered User.
Local time
Today, 13:37
Joined
Aug 6, 2002
Messages
44
Actually even my so-called smart-alec solution was not nearly smart enough and a good deal hairier than I originally thought.

I was awake half the night working this one out (sad nerd), so here is the logic - if I have the time/energy & anybody is actually likely to use it I'll try coding it properly.

You need to check for leap years both at the DOB and current date end, taking account that the month also has an effect.

I was using the check IIf(Year(Date())/4=Round(Year(Date())/4,0) to work out the presence of a leap year; this can be expanded. You need the divider of 365.25 to get the years, but differing amounts when working with days left over.

The logic is:

if ((DOB is a leap year AND month(DOB)<=FEB) AND (current year is a leap year AND current month > FEB)),

Calculate year, and use 367 to work out days

ELSE if ((DOB is a leap year AND month(DOB)<=FEB) OR (current year is a leap year AND current month > FEB)),

Calculate year, and use 366 to work out days

ELSE [neither year is a leap year] Calculate year, and use 365 to work out days.

Even this solution will fail in the year 3000 which I believe is not a leap year despite being divisible by 4...

Probably best to use the slightly less flash

age1: Int((Date()-[dateval])/365.25) & "yr " & Int(((Date()-[dateval])/365.25-Int((Date()-[dateval])/365.25))*12) & "months"

which loses the leap year problem altogether.....
 

clive2002

Registered User.
Local time
Today, 13:37
Joined
Apr 21, 2002
Messages
91
Maybe i'm missing something, but.......

I cant see whats wrong with this.

AGE: Format(Now(),"yyyy")-Format([Table1]![DOB],"yyyy")
 

raskew

AWF VIP
Local time
Today, 08:37
Joined
Jun 2, 2001
Messages
2,734
Alexandre-

Found the second example you pointed to on the Access Web, posted by Tim Walters, particularly fascinating since it does the same thing as my code, with far fewer lines, using a totally different methodology. It's worth studying. Didn't understand how the vYears, vMonths, vDays were to be implemented, so I rewrote it as a function, specifying vYears, vMonths and vDays as variables.
Code:
Function CalcAge(vDate1 As Date, vdate2 As Date)
    ' from [url]http://www.mvps.org/access/datetime/date0001.htm[/url]
    ' posted by Tim Walters, modified by Bob Askew
    ' Comments  : calculates the age in Years, Months and Days
    ' Parameters:
    '    vDate1 - Date of Birth (DOB) ' the earlier date
    '    vDate2 - Date to calculate age based on 'the later date
    
    Dim vYears As Integer, vMonths As Integer, vDays As Integer
    vMonths = DateDiff("m", vDate1, vdate2)
    vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
    If vDays < 0 Then
        ' weird way that DateDiff works, fix it here
        vMonths = vMonths - 1
        vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
    End If
    vYears = vMonths \ 12 ' integer division
    vMonths = vMonths Mod 12 ' only want leftover less than one year
    CalcAge = vYears & "." & vMonths & "." & vDays
End Function

Clive2002-

The thread shifted from just determining an age in years, to calculating one in years.months.days.

What's 'wrong' with your solution is that it fails to take into consideration
month and days. It's based strictly upon year. Consider this:
your DOB is 12/31/83. We want to calculate how old you were on
1/1/00. Your calculation will tell us 17, while in fact you didn't turn 17 until
12/31/00.
 

danson

Registered User.
Local time
Tomorrow, 00:37
Joined
Apr 6, 2004
Messages
15
More than a Date of Birth

Hi All

I am really getting frustrated trying to solve the following problem –I am also new to code.

Have created an access data base that needs to calculate age from date of birth with years and months
I used the following code in the Gereral Decs (a mate sent it to me as is)

Option Explicit

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

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

varAge = DateDiff("yyyy", varBirthDate, Now)
If Date < DateSerial(Year(Now), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

Function AgeMonths(ByVal StartDate As String) As Integer

Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function

In the form I have 6 text boxes in the agetxt I have entered the following code
=Age([txtDOB]) & " years " & AgeMonths([txtDOB]) & " months"
Returns eg 3 years 6 month (depend on the DOB)
Appears to work fine

Problem arises when I try to calculate between DOB and Date of test1 – I need the age in years and months again.
I have 4 different test dates to DOB calculations that need to be done

I
a)Don’t know if I can utilise the above code for the DOB to testdate caculations
b) Am so new to code I need step by step guidance
c) Have been trying to work out an answer to this for so long that I am now totally confused
d)Am thankful to have searched my way here
 
Last edited:
R

Rich

Guest
I've only tested this very quickly

Option Compare Database
Option Explicit

Function Age(varBirthDate As Variant, varDteTo As Date) As Integer
Dim varAge As Variant
On Error Resume Next
If IsNull(varBirthDate) Then Age = 0: Exit Function

varAge = DateDiff("yyyy", varBirthDate, varDteTo)
If varDteTo < DateSerial(Year(varDteTo), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)

End Function

Function AgeMonths(ByVal StartDate As String, varDteTo As Date) As Integer

Dim tAge As Double

tAge = (DateDiff("m", StartDate, varDteTo))
If (DatePart("d", StartDate) > DatePart("d", varDteTo)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


I've also altered the control source of the unbound textbox slightly

=IIf(Age([fldDOB],[fldDteTo])=0," ",Age([fldDOB],[fldDteTo]) & " years ") & AgeMonths([fldDOB],[fldDteTo]) & " months"
 

TJBernard

Registered User.
Local time
Today, 13:37
Joined
Mar 28, 2002
Messages
176
Thank you very much for your help with this. I have tried many different examples, and have been able to find a fix to this issue with your help.

Again this message board comes through for me, and I am very appreciative.

Again, thank you,

TJBernard
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:37
Joined
Feb 19, 2002
Messages
42,970
Maybe i'm missing something, but.......

--------------------------------------------------------------------------------

I cant see whats wrong with this.

AGE: Format(Now(),"yyyy")-Format([Table1]![DOB],"yyyy")
= you are missing two things:
1. Now() is date + time of day. You should ALWAYS use Date() in calculations where you are only interested in the date. Don't confuse yourself by including time.
2. This function will return the incorrect result if a person's birthday has not arrived yet for a given year. So If you were born on April 10th, 2000, you would still be three years old today. You won't be four until tomorrow. Your calculation would show you as 4 from Jan 1st. If your actual birthday isn't until Dec 31st, the error approaches an entire year.

From the samples db section, download my useful date samples db. There is an age function that returns the correct age in years. There is another function that will return the difference between two dates in whatever units you want. Both are functions so you can just add them into one of your modules and just call them while passing the two dates. The db contains an interface form that shows you how the functions may be used.
 

danson

Registered User.
Local time
Tomorrow, 00:37
Joined
Apr 6, 2004
Messages
15
Rich said:
I've only tested this very quickly

Option Compare Database
Option Explicit

Function Age(varBirthDate As Variant, varDteTo As Date) As Integer
Dim varAge As Variant
On Error Resume Next
If IsNull(varBirthDate) Then Age = 0: Exit Function

varAge = DateDiff("yyyy", varBirthDate, varDteTo)
If varDteTo < DateSerial(Year(varDteTo), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)

End Function

Function AgeMonths(ByVal StartDate As String, varDteTo As Date) As Integer

Dim tAge As Double

tAge = (DateDiff("m", StartDate, varDteTo))
If (DatePart("d", StartDate) > DatePart("d", varDteTo)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


I've also altered the control source of the unbound textbox slightly

=IIf(Age([fldDOB],[fldDteTo])=0," ",Age([fldDOB],[fldDteTo]) & " years ") & AgeMonths([fldDOB],[fldDteTo]) & " months"


Thanks

--------------------------------------------------------------------------------

Hi Rick

Thanks for prompt reply.
I must be 2 old or really thick because I just dont get how to make this work.
Pasted it into the textbox that is to store the age of the kid at the date of test 1 (txtAgeTest1) I get a message saying expression entered has a function containing the wrong number or arguments.

I am missing something - just don't know what

If you have any advice or help I would appreciate it

Frustrated by my own incompetence
Danson
 
R

Rich

Guest
Please use the Post Reply button we don't need to see the quote from the previous post.

A; you shouldn't store this calculation, it can be calculated at any time from the DOB and date of exam fields, in any case setting the textbox control source to the Function means it's an unbound textbox and won't be saved to the table.

B; did you copy the expression as posted, just changing the field names for your own?
 

Zubair Mughal

New member
Local time
Today, 18:37
Joined
Sep 10, 2020
Messages
3
I have a Field to store the Age of an individual. The user wants this Age Field to autocalculate from a Date Of Birth Field also stored in the table. I was trying to think of a way to subtract the DOB field from the Date() function as the Default value of the Age Field. I could not find a way to do this. Am I on the right track? Is this even possible or feasible? Let me know if anyone has tried this before.

Thank you in advance,

T.J.
Kindly share how to STORE age in the Age Field of the Table. Thanks
 

Users who are viewing this thread

Top Bottom