Calculate the age (1 Viewer)

G

gstylianou

Registered User.
Local time
Today, 08:20
Joined
Dec 16, 2013
Messages
357
Hi all,

Dear friends i nees help on haw can calculate the age into query expression. I need the age as follow: 18 years, 11 months and 14 days..! Is it possible to have this result into query?

Thanks in advance
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:20
Joined
Oct 29, 2018
Messages
21,454
Hi. I just posted a link to a similar question. Maybe you could also give this a try.
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:20
Joined
Feb 28, 2001
Messages
27,140
The question is how you will use it. Making a date data type hold exactly some specific number of years, months, and days will be trivial. The question will always be "so now you have this value... what is next for it?"

You can do something as simple as

Code: 
TotalAge = DateAdd( "yyyy", 18, 0)
TotalAge = DateAdd( "m", 11, TotalAge )
TotalAge = DateAdd( "d", 14, TotalAge )

... or

TotalAge = DateAdd( "d", 14, ( DateAdd( "m", 11, ( DateAdd( "yyyy", 18, 0 ) ) )

Unfortunately, when talking about a relative amount of time that isn't a specific date, there is no intrinsic Access format for intervals that span years. That is, you can do hours, minutes, and seconds as nn:nn:nn (hh:nn:ss, actually). But trying to express that interval as, say, 14-11-18 or something like that is not going to be automatically understood by VBA.

The "native" unit of time for Access is days. Odds are that if you applied the formula I showed, you would get a number of days representing 18 years that included 4 days of Leap Years. I believe that the number of days for 11 months will depend on what you used as a starting date. (I.e. if your starting date was in March, 11 months does not include February and so I'm not sure what you will get without just trying it.)
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:20
Joined
May 7, 2009
Messages
19,231
SELECT [First Name], [Last Name], [Date of Birth], Age([Date Of Birth]) As Age
FROM yourTable;
Code: 
' agp
Public Function Age(Date1 As Date, Optional Date2 As Date = 0) As String
Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date
If Date2 = 0 Then Date2 = Date
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
Day1 = Day(Date2) - Day(Date1)
If Day1 < 0 Then
    Month_1 = Month_1 - 1
    Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
End If
If Year1 > 0 Then
    Age = Year1
    If Year1 > 1 Then
        Age = Age & " years "
    Else
        Age = Age & " year "
    End If
    If Month_1 > 0 And Day1 = 0 Then
        Age = Age & " and "
    End If
End If
If Month_1 > 0 Then
    Age = Age & Month_1
    If Month_1 > 1 Then
        Age = Age & " months "
    Else
        Age = Age & " month "
    End If
End If
If Day1 > 0 Then
    Age = Age & " and " & Day1
    If Day1 > 1 Then
        Age = Age & " days"
    Else
        Age = Age & " day"
    End If
End If
Age = Trim(Replace(Age, "  ", " "))
End Function
 
G

gstylianou

Registered User.
Local time
Today, 08:20
Joined
Dec 16, 2013
Messages
357
Dear all,

First i would like to thanks for your help. But, i need to have the age result as a number like the following example:

Because the age accuracy it's very critical issue for my database because it concerns a pediatrician software which using child development graphs direct from the World Health Organization, the exact results of the child age must be calculated as follows:

Example 1:
[DateAdded]: 10/10/2019
[DOB] = 01/10/2019

RESULTS MUST BE
[FirstAge]: 0,010
* Results: 0, 010 (0 = years , 0 months and 10 days)

[TodayAge]: 0,017
* Results: 0, 017 (0 = years , 0 months and 17 days)

Example 2:
[DateAdded]: 01/06/1999
[DOB] = 22/04/1996

RESULTS MUST BE
[FirstAge]: 3,222
* Results: 3,222 (3 = years , 2 months and 22 days)

[TodayAge]: 23,1017
* Results: 23, 1017 (23 = years , 10 months and 17 days)
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 06:20
Joined
Sep 21, 2011
Messages
14,231
So use arnelgp's code and then apply your requirements yourself.?
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:20
Joined
Feb 28, 2001
Messages
27,140
If you write a function in a general module and make it public, you can call that function from a query as part of the SELECT field-list clause. The function takes the place of a field. A function in this context MUST return a value. This value might as well be in the format you want. I.e if you wanted a true number, the function should return a number. If you want a formatted string, the function should return a formatted string.

Your format is specialized enough that you will certainly need to write a string function to do what you want. The good news is that it won't be that difficult since you only want age to a resolution of days. If you get the age in days using DateDiff() and turn that into a LONG integer, then you can use simple integer division and remaindering to do all the math you need.

Look at what Arnel showed you. He formatted the output a little differently than the samples you showed us, but that kind of function is going to be what you want.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:20
Joined
May 7, 2009
Messages
19,231
Code: 
SELECT Table1.DateAdded, Table1.DOB, DateDiff("d",[DOB],[DateAdded]) AS FirstAge, Age([DOB],[DateAdded]) AS FirstAgeText, DateDiff("d",[DOB],Date()) AS TodayAge, Age([DOB],Date()) AS TodayAgeText
FROM Table1;
modify the code in post #5.
Code: 
Public Function Age(Date1 As Date, Optional Date2 As Date = 0) As String
    Dim Year1   As Integer
    Dim Month_1 As Integer
    Dim Day1    As Integer
    Dim temp    As Date
    If Date2 = 0 Then Date2 = DATE
    temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
    Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
    Day1 = Day(Date2) - Day(Date1)
    If Day1 < 0 Then
        Month_1 = Month_1 - 1
        Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
    End If
    Age = Year1
    If Year1 > 1 Then
        Age = Age & " years "
    Else
        Age = Age & " year "
    End If
    If Month_1 > 0 And Day1 = 0 Then
        Age = Age & " and "
    End If
    Age = Age & Month_1
    If Month_1 > 1 Then
        Age = Age & " months "
    Else
        Age = Age & " month "
    End If
    Age = Age & " and " & Day1
    If Day1 > 1 Then
        Age = Age & " days"
    Else
        Age = Age & " day"
    End If
    Age = Trim(Replace(Age, "  ", " "))
End Function
 
G

gstylianou

Registered User.
Local time
Today, 08:20
Joined
Dec 16, 2013
Messages
357
arnelgp said:
Code: 
SELECT Table1.DateAdded, Table1.DOB, DateDiff("d",[DOB],[DateAdded]) AS FirstAge, Age([DOB],[DateAdded]) AS FirstAgeText, DateDiff("d",[DOB],Date()) AS TodayAge, Age([DOB],Date()) AS TodayAgeText
FROM Table1;
modify the code in post #5.
Code: 
Public Function Age(Date1 As Date, Optional Date2 As Date = 0) As String
    Dim Year1   As Integer
    Dim Month_1 As Integer
    Dim Day1    As Integer
    Dim temp    As Date
    If Date2 = 0 Then Date2 = DATE
    temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
    Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
    Day1 = Day(Date2) - Day(Date1)
    If Day1 < 0 Then
        Month_1 = Month_1 - 1
        Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
    End If
    Age = Year1
    If Year1 > 1 Then
        Age = Age & " years "
    Else
        Age = Age & " year "
    End If
    If Month_1 > 0 And Day1 = 0 Then
        Age = Age & " and "
    End If
    Age = Age & Month_1
    If Month_1 > 1 Then
        Age = Age & " months "
    Else
        Age = Age & " month "
    End If
    Age = Age & " and " & Day1
    If Day1 > 1 Then
        Age = Age & " days"
    Else
        Age = Age & " day"
    End If
    Age = Trim(Replace(Age, "  ", " "))
End Function
Click to expand...


Hello again and I apologize for the inconvenience because of my little knowledge on vba,

I tried all but i'm getting error into query. Following is the modified code as well


Option Compare Database
Public Function xcalcAge(DOB As Date, Optional DateAdded As Date = 0) As String

Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date

If DateAdded = 0 Then DateAdded = Date
temp = DateSerial(Year(DateAdded), Month(DOB), Day(DOB))
Year1 = Year(DateAdded) - Year(DOB) + (temp > DateAdded)
Month_1 = Month(DateAdded) - Month(DOB) - (12 * (temp > DateAdded))
Day1 = Day(DateAdded) - Day(DOB)

If Day1 < 0 Then
Month_1 = Month_1 - 1
Day1 = Day(DateSerial(Year(DateAdded), Month(DateAdded) + 1, 0)) + Day1 + 1
End If

If Year1 > 0 Then
Age = Year1
If Year1 > 1 Then
Age = Age & " years "
Else
Age = Age & " year "
End If
If Month_1 > 0 And Day1 = 0 Then
Age = Age & " and "
End If
End If

If Month_1 > 0 Then
Age = Age & Month_1
If Month_1 > 1 Then
Age = Age & " months "
Else
Age = Age & " month "
End If
End If

If Day1 > 0 Then
Age = Age & " and " & Day1
If Day1 > 1 Then
Age = Age & " days"
Else
Age = Age & " day"
End If
End If

Age = Trim(Replace(Age, " ", " "))
End Function
 

Attachments

  • ERROR.JPG
    ERROR.JPG
    93.5 KB · Views: 100
Last edited:
G

gstylianou

Registered User.
Local time
Today, 08:20
Joined
Dec 16, 2013
Messages
357
I've made some corrections to the code and shown data to the query but no age results
 

Attachments

  • NoAgeCalc.jpg
    NoAgeCalc.jpg
    79.4 KB · Views: 108
  • noAgeCalcDesign.JPG
    noAgeCalcDesign.JPG
    71.3 KB · Views: 98
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:20
Joined
May 7, 2009
Messages
19,231
did you put the function in a Module (module name must not be same name as the function).
 
G

gstylianou

Registered User.
Local time
Today, 08:20
Joined
Dec 16, 2013
Messages
357
arnelgp said:
did you put the function in a Module (module name must not be same name as the function).
Click to expand...

Yes, i changed the function name

Option Compare Database
Public Function xcalcAge(DOB As Date, Optional DateAdded As Date = 0) As String

Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date

If DateAdded = 0 Then DateAdded = Date
temp = DateSerial(Year(DateAdded), Month(DOB), Day(DOB))
Year1 = Year(DateAdded) - Year(DOB) + (temp > DateAdded)
Month_1 = Month(DateAdded) - Month(DOB) - (12 * (temp > DateAdded))
Day1 = Day(DateAdded) - Day(DOB)

If Day1 < 0 Then
Month_1 = Month_1 - 1
Day1 = Day(DateSerial(Year(DateAdded), Month(DateAdded) + 1, 0)) + Day1 + 1
End If

If Year1 > 0 Then
Age = Year1
If Year1 > 1 Then
Age = Age & " years "
Else
Age = Age & " year "
End If
If Month_1 > 0 And Day1 = 0 Then
Age = Age & " and "
End If
End If

If Month_1 > 0 Then
Age = Age & Month_1
If Month_1 > 1 Then
Age = Age & " months "
Else
Age = Age & " month "
End If
End If

If Day1 > 0 Then
Age = Age & " and " & Day1
If Day1 > 1 Then
Age = Age & " days"
Else
Age = Age & " day"
End If
End If

Age = Trim(Replace(Age, " ", " "))
End Function
 
Minty

Minty

AWF VIP
Local time
Today, 06:20
Joined
Jul 26, 2013
Messages
10,366
Age was the name of the returned item, so you will have to change every reference from Age in the function to xcalcAge
 
Last edited:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 06:20
Joined
Sep 21, 2011
Messages
14,231
I just tried it out and it works fine for me?
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:20
Joined
Feb 19, 2002
Messages
43,223
I thought I posted this yesterday but perhaps there is a similar thread or you posted the same question in a different forum. This is a database with a number of date functions that you can pull out and use in your own apps. One is age.
 

Attachments

  • UsefulDateFunctions180618.zip
    231 KB · Views: 95
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:20
Joined
Feb 28, 2001
Messages
27,140
A couple of comments...

First, you could fix this with two lines of code.

At the top of the routine among the other declarations, add

Dim Age as String

Just before the End Sub, add

xcalcAge = Age

Now, the other thing: You SHOULD get an error when compiling that, but you didn't report such an error. Therefore you must be running without Option Explicit declared in the module. This is not good for anyone, but it is particularly bad for a VBA beginner.

The reason is that with Option Explicit you can let the compiler warn you that you have spelled something incorrectly or have forgotten to declare something. This code would have worked (with my second suggested modification to properly return the value) but in so doing, would have used the variable Age as being of type Variant. That's why I also included a formal declaration of the Age variable as a correction.

Undeclared variables in the absence of Option Explicit will be dynamically created as the Variant data type. Variants can be anything anytime and will SILENTLY accept values that would otherwise be incompatible with your original purpose. If you used a declared variable of a type other than variant, then assigning an incompatible value would result in a "mixed mode" warning.
 
G

gstylianou

Registered User.
Local time
Today, 08:20
Joined
Dec 16, 2013
Messages
357
I would like to thanks all for the helping....I will try to find a way to modify the vba code as you suggest.

thanks
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:20
Joined
Oct 29, 2018
Messages
21,454
gstylianou said:
I would like to thanks all for the helping....I will try to find a way to modify the vba code as you suggest.

thanks
Click to expand...
Hi. Good luck. I'm just curious, could you post a link to the child development standards from the WHO? I'm just curious about the formatting requirements. For example, is the comma a requirement or not? Thanks!
 
You must log in or register to reply here.

Similar threads

L
Solved Problem calculating correct age from birth to death
Replies
11
Views
320
GPGeorge
GPGeorge
J
Solved Age Deomgraphics
2
Replies
27
Views
642
GaP42
G
H
convert age to date of birth in three fields: year, month, and day.
Replies
3
Views
482
Pat Hartman
P
G
Import data from file
Replies
8
Views
472
glenquagmire31
G
N
Solved Calculate approximate birth month and year from approximate age in year and month
Replies
6
Views
512
nirmal
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom