472,806 Members | 1,853 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,806 software developers and data experts.

Calculate age in years and months in Query

First let me say two things--I have been searching and trying different examples for two days and have not been able to make anything I find work (I'm sure due to my lack of knowledge) and second, if anyone that is kind enough to try to help me assumes I won't need every single step included to make it work, they are probably wrong. I want to put a field in a query that will calculate age in years and months to the current date so that I can use it for more than one form or report. What I am currently using (shown below) gives only years and now I'd like months as well.

Age: DateDiff("yyyy",[Date Shipped from Factory],Now())+Int(Format(Now(),"mmdd")<Format([Date Shipped from Factory],"mmdd"))

If it would be easier to put an unbound control in my form or report, I would be willing to do that as well. Any assistance would be appreciated.
Nov 10 '15 #1
16 9399
Rabbit
12,516 Expert Mod 8TB
Calculate the difference in months and then mod the result by 12.
Nov 10 '15 #2
Apparently even when done accurately (which mine is not--I'm only guessing how to modify my current formula) it appears that will give me numbers and tenths rather than years and months. And with my best guess about how to modify the formula, I'm either getting 3.0, which is the number of years but doesn't include any months, 44.0 which is apparently the number of months only, and won't really work or it seems like I got 3.7, but now for the life of me I can't remember what I changed to get that.
Nov 10 '15 #3
Rabbit
12,516 Expert Mod 8TB
You combine what you use to get the years with my algorithm in post #2 to get the months. When you put them together, you get the years and months.
Nov 10 '15 #4
I'm sorry, but I have absolutely no idea what you mean. I was hoping I could get a "change this to that" answer, or copy and use this. Access is a very small part of what I do, so I have very limited opportunity to learn much, and everything I do learn I have to find an example somewhere that I can alter, and when you really have no VBA experience, altering something is often harder than someone might expect. Even as plain as the nose on your face isn't plain to someone who has never seen a nose. Thanks for your time and patience--I'll probably just stay with years or export to Excel where my experience level is much higher.
Nov 10 '15 #5
Rabbit
12,516 Expert Mod 8TB
Just take it step by step. Also, no one said anything about VBA.

Calculate the difference in months
You already know how to do this since you're calculating years.
Expand|Select|Wrap|Line Numbers
  1. DateDiff("m",[Date Shipped from Factory],Now())
and then mod the result by 12.
Expand|Select|Wrap|Line Numbers
  1. DateDiff("m",[Date Shipped from Factory],Now()) Mod 12
put them together
I'll leave this final part for you as an exercise. Hint: To put two things together, use the & operator.
Nov 10 '15 #6
I tried this in pieces to try to understand better. My first record should be 3 years 8 months. When I use only the first line, I get 44 for my first answer, which would be the correct number of months. Then when I insert the & and then paste the second line, I get 448. Not sure what that could be. I tried leaving out the second statement except for the mod 12, but that gave me an error. I tried the second line alone, and I got an 8, which would be how many additional months the first record is past three years, but nothing that gives me the answer 3 years 8 months. I suppose I could use two separate fields for the query and then see if Access will concatenate like Excel in my forms and reports.
Nov 10 '15 #7
zmbd
5,501 Expert Mod 4TB
Take your first equation Eq1
Take your second equation Eq2 (hint Mod12)
Create a calculated field

EXP1: (eq1) & "yrs " & (eq2) & "mo"
Nov 10 '15 #8
Aftab Ahmad
42 32bit
ZMBD & Rabbit.
This is not correct solution. I was searching for the same long time but nothing found regarding calculate age with months and year even I have done it in Excel.
Now come to the formula. If my date of birth is 05/03/1987 then my formula (DateDiff) wchich is mentioned above by both of you will work like this:
Years: 28
Month: 336
And when we combine them using & separator, it looks like for example,
=[years] & "/" & [months] = 28/336 which is not correct way because he wants to calculate years and months i.e. 28(Years)7(months). May be I am not correct, your assistance anyhow will Preferable and loving for me. Thanks
Nov 11 '15 #9
strive4peace
39 Expert 32bit
44 mod 12 = 8, which is the months left over
Nov 11 '15 #10
zmbd
5,501 Expert Mod 4TB
@ Aftab Ahmad
Just what are you talking about here?
The ampersand is not a mathematical operand in this case. It is used to combine two strings (concatenation of strings).

>> Your returned value of 336months is most courious and I do think that you have most likely mis-keyed your values.
>> where did you come up with:
=[years] & "/" & [months] = 28/336
not only did neither Rabbit nor I suggest or provide such a formula, it will not evaluate as you've suggested. Instead, the engine will take the [Year], backslash, and [Month] as literal string value and return the concatenation of these as a string value without any evaluation in either a calculated field or control.

Furthermore:
A) If we use your "birth day" of 05/03/1987 and today's date of 11/10/2015 and calculate the number of months we get:
datediff("m",#05/03/1987#,#11/10/2015#)=342mo

a1) Quick verification of the elapsed years using the ever so classic method of:
[HistoricalYear]-[CurrentYear] == 2015-1987 == 28years
using the conversion of (12mo/yr) gives us 336 months plus the residual count between May to December (ignoring residual days) of 6 months for a grand total of 336+6=342mo

B) Using the integer divisor operand "\" we calculate the number of years using the base conversion of (1yr/12mo)
342\12=28yrs

b1) We've already verified this result under Step-a1

C) Now we need the remainder that would have been returned from the prior division if we had used the standard divisional operand (returning 28.5yrs). This is most easily determined by using the Mod() to return the modulus value against 12 of the 342
342 mod 12= 6

Quick verification:
==(342 - (28 * 12) )
==(342 - (336) )
== 6

Both in agreement with the fractional part of 0.5yrs == 6mo

+ The remainder of this situation would be concatenation of the results from Step B and Step C

B() & "yrs " & C() & "mo"
==
>> 28 & "yrs " & 6 & "mo"
==
"28yrs 6mo"

You can verify this yourself in any VBA enabled Office program by pressing <ctrl><g> and typing the "?" followed by the above formulas to return the values of the calculations.


@strive4peace
That was already implied in Rabbit's post#2
The question has become does CindySue know how to create the calculated field, to whit, I have provided a link to a fairly decent tutorial that describes each step to a reasonable degree (IMHO).
Nov 11 '15 #11
strive4peace
39 Expert 32bit
obviously English is not Aftab's first language ... just trying to be polite.
Nov 11 '15 #12
MikeTheBike
639 Expert 512MB
Hi All

In the original question it is unclear if the DAY of shipping should be taken in to account to calculate the Age as you would a persons age.
So I thought I would have a go.

There are obviously a number of way of doing this, including user define function which I now from experiance are alway significantly slower.
I have used a slightly different approach to calculater the basic years (ie integer divide of month) and came up with this.

Expand|Select|Wrap|Line Numbers
  1. Age: DateDiff("m", [Date Shipped from Factory], Date()) \ 12 + (Month([Date Shipped from Factory]) = Month(Date()) And Day([Date Shipped from Factory]) > Day(Date())) & " Years and " & DateDiff("m", [Date Shipped from Factory], Date()) Mod 12 + (Month([Date Shipped from Factory]) = Month(Date()) And Day([Date Shipped from Factory]) > Day(Date())) * -11 & " Months"
This boolen calc
Expand|Select|Wrap|Line Numbers
  1. (Month({Date Shipped from Factory]) = Month(Date) And Day({Date Shipped from Factory]) > Day(Date()))
returns -1 if True and 0 if False.

Not sure if it is relevant, but it was interesting!


MTB
Nov 11 '15 #13
Aftab Ahmad
42 32bit
Yes sorry my bad English :(
Nov 11 '15 #14
tsimeone
1 Bit
Hi,

Above is great, how do i add Days on to the end of Years and Months?

Many Thanks

Tom
Feb 4 '21 #15
isladogs
437 Expert Mod 256MB
Including days significantly adds to the complexity.
One solution is given in post #24 of this recent thread how-do-i-overcome-error-expression-you-entered-contains-invalid-syntax. However, do read the rest of the thread to see some other suggestions.
Feb 4 '21 #16
cactusdata
208 Expert 128KB
You can use these two functions:

Expand|Select|Wrap|Line Numbers
  1. ' Returns the difference in full months from DateOfBirth to current date,
  2. ' optionally to another date.
  3. ' Returns by reference the difference in days.
  4. ' Returns zero if AnotherDate is earlier than DateOfBirth.
  5. '
  6. ' Calculates correctly for:
  7. '   leap Months
  8. '   dates of 29. February
  9. '   date/time values with embedded time values
  10. '   any date/time value of data type Date
  11. '
  12. ' DateAdd() is, when adding a count of months to dates of 31th (29th),
  13. ' used for check for month end as it correctly returns the 30th (28th)
  14. ' when the resulting month has 30 or less days.
  15. '
  16. ' 2015-11-24. Gustav Brock, Cactus Data ApS, CPH.
  17. '
  18. Public Function AgeMonthsDays( _
  19.     ByVal DateOfBirth As Date, _
  20.     Optional ByVal AnotherDate As Variant, _
  21.     Optional ByRef Days As Integer) _
  22.     As Long
  23.  
  24.     Dim ThisDate    As Date
  25.     Dim Months      As Long
  26.  
  27.     If IsDate(AnotherDate) Then
  28.         ThisDate = CDate(AnotherDate)
  29.     Else
  30.         ThisDate = Date
  31.     End If
  32.  
  33.     ' Find difference in calendar Months.
  34.     Months = DateDiff("m", DateOfBirth, ThisDate)
  35.     If Months < 0 Then
  36.         Months = 0
  37.     Else
  38.         If Months > 0 Then
  39.             ' Decrease by 1 if current date is earlier than birthday of current year
  40.             ' using DateDiff to ignore a time portion of DateOfBirth.
  41.             If DateDiff("d", ThisDate, DateAdd("m", Months, DateOfBirth)) > 0 Then
  42.                 Months = Months - 1
  43.             End If
  44.         End If
  45.         ' Find difference in days.
  46.         Days = DateDiff("d", DateAdd("m", Months, DateOfBirth), ThisDate)
  47.     End If
  48.  
  49.     AgeMonthsDays = Months
  50.  
  51. End Function
  52.  
  53.  
  54. ' Formats the output from AgeMonthsDays.
  55. '
  56. ' 2020-10-05. Gustav Brock, Cactus Data ApS, CPH.
  57. '
  58. Public Function FormatAgeYearsMonthsDays( _
  59.     ByVal DateOfBirth As Date, _
  60.     Optional ByVal AnotherDate As Variant) _
  61.     As String
  62.  
  63.     Dim Years       As Integer
  64.     Dim Months      As Integer
  65.     Dim Days        As Integer
  66.     Dim YearsLabel  As String
  67.     Dim MonthsLabel As String
  68.     Dim DaysLabel   As String
  69.     Dim Result      As String
  70.  
  71.     Months = AgeMonthsDays(DateOfBirth, AnotherDate, Days)
  72.     Years = Months \ MonthsPerYear
  73.     Months = Months Mod MonthsPerYear
  74.  
  75.     YearsLabel = "year" & IIf(Years = 1, "", "s")
  76.     MonthsLabel = "month" & IIf(Months = 1, "", "s")
  77.     DaysLabel = "day" & IIf(Days = 1, "", "s")
  78.  
  79.     ' Concatenate the parts of the output.
  80.     Result = CStr(Years) & " " & YearsLabel & ", " & CStr(Months) & " " & MonthsLabel & ", " & CStr(Days) & " " & DaysLabel
  81.  
  82.     FormatAgeYearsMonthsDays = Result
  83.  
  84. End Function
Feb 5 '21 #17

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Tencip | last post by:
Hi everyone, I'm trying to build a simple script that does the following. It should find today's month and year, and then go into a DB query string and look for all records that are from this...
6
by: carl.barrett | last post by:
Hi, I have a continuous form based on a query ( I will also be creating a report based on the same query). There are 2 fields: Date Obtained and Date Of Expiry I want a further 3 columns...
5
by: Juan | last post by:
Hi everyone, is there a function that calculates the exact amount of Years, Months, and Days between two days? TimeDiff is not good enough, since it calculates, for example: Date 1: Dec....
8
by: Jose | last post by:
Exists a function that determined between two dates the years,months and days? Thanks a lot.
4
by: MCLR | last post by:
Good day to all: I found your website a couple of days ago as I was searching for resources that can help within my particular problem in writing a script for an Access Query. The issue is to...
15
karthickkuchanur
by: karthickkuchanur | last post by:
Hai sir, i assigned to calculate the age from current date ,i have the date of birth of employee from that i have to calculate the age dynamically, i refer to google it was confusing...
2
by: Louise Harrison | last post by:
I had read a previous article on calculating the number of years, months and days between 2 dates. They had used the DateDiff with a start date and an end date, similiar like below statement: ...
1
by: taunt | last post by:
Hello I have a table that looks like this: <td><div align="center"><strong><cfset dtDiff = (qsearch.ReceiptDate - qsearch.LAST_SOLD) /> #DateDiff( "yyyy", "12/30/1899", dtDiff )# Years,...
3
by: Evelyn Jones | last post by:
I would like for access to calculate how long someone has been working with the company based on their date of hire. Is there some way I can set it up so when I run my query it will look at their...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.