467,166 Members | 1,133 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,166 developers. It's quick & easy.

how do i overcome the error the expression you entered contains invalid syntax

Bit
the expression is
age(Year [Consultation date]-[Year of birth])
please help
3 Weeks Ago #1
  • viewed: 1821
Share:
34 Replies
NeoPa
Expert Mod 16PB
Don't overlook the parentheses ;-)

Age() and Year() functions both use them.
3 Weeks Ago #2
isladogs
Expert 128KB
Also, do you have your own Age function as there isn't one built into Access
3 Weeks Ago #3
twinnyfo
Expert Mod 2GB
My initial stab is that if Age is a variable, the following should work:

Expand|Select|Wrap|Line Numbers
  1. Age = Year([Consultation date]-[Year of birth])
But, like others, this is simply a guess as to what OP is after.
3 Weeks Ago #4
isladogs
Expert 128KB
@twinnyfo:
Bracketing.... I expect you meant:

Expand|Select|Wrap|Line Numbers
  1. Age = Year([Consultation date])-[Year of birth]
Of course that is only accurate if the patient has already had their birthday this year.
3 Weeks Ago #5
twinnyfo
Expert Mod 2GB
Isladogs,

Ahh yes! I was misreading [Year of Birth] as [Date of Birth].

Good catch!

Your solution should definitely work!
3 Weeks Ago #6
isladogs
Expert 128KB
Personally, I prefer something that allows for whether the person has had their birthday this year. For example, any of the methods listed at: Calculate Age for Access
3 Weeks Ago #7
twinnyfo
Expert Mod 2GB
Agreed - comparing (or adding/subtracting) dates with years just causes problems from the start.
3 Weeks Ago #8
ADezii
Expert 8TB
Here is the Function that I like to use. It factors in what isladogs already stated in Post# 7 about the Birthday occurring in the current Year. It also allows an optional As Of Date as an Argument:
Expand|Select|Wrap|Line Numbers
  1. Function fCalculateAge(varDOB As Variant, Optional varAsOfThisDate As Variant) As Variant
  2. 'Purpose:   Return the Age in years.
  3. 'Arguments: varDOB = Date Of Birth
  4. 'varAsOfThisDate = the date to calculate the age at, or today if missing.
  5.  
  6. 'The DateDiff() Function simply subtracts the Year parts of the Dates, without
  7. 'reference to the Month or Day. This means we need to subtract one if the person
  8. 'has not has their birthday this year. This can be handled by a Boolean Expression
  9. 'that returns either -1 or 0 depending on whether or not an individual's
  10. 'birthday occurred within the Year.
  11.  
  12. 'Return:    Whole number of years.
  13. Dim dteDOB As Date
  14. Dim dteAsOf As Date
  15. Dim dteBDay As Date     'Birthday in the year of calculation.
  16.  
  17. fCalculateAge = Null    'Initialize to Null
  18.  
  19. 'Validate Parameters
  20. If IsDate(varDOB) Then
  21.   dteDOB = varDOB
  22.  
  23.   If Not IsDate(varAsOfThisDate) Then  'Date to calculate age from.
  24.     dteAsOf = Date      'Use the Current Date
  25.   Else
  26.     dteAsOf = varAsOfThisDate       'Valid As Of Date
  27.   End If
  28.  
  29.   If dteAsOf >= dteDOB Then      'Calculate only if it's after person was born.
  30.     dteBDay = DateSerial(Year(dteAsOf), Month(dteDOB), Day(dteDOB))
  31.       fCalculateAge = DateDiff("yyyy", dteDOB, dteAsOf) + (dteBDay > dteAsOf)
  32.   End If
  33. End If
  34. End Function
3 Weeks Ago #9
NeoPa
Expert Mod 16PB
Am I missing something? Calculating age in the normal way is surely as simple as :
Expand|Select|Wrap|Line Numbers
  1. Public Function Age(datBirth As Date _
  2.                   , Optional ByVal datNow As Date=Date()) As Long
  3.     Age = DateDiff("m", datBirth, datNow) \ 12
  4. End Function
3 Weeks Ago #10
isladogs
Expert 128KB
Hi @NeoPa
Yes I think you are missing something.
Here are two examples using your expression.
They wrongly give the same result even though the second example hasn't yet had a birthday this year
Expand|Select|Wrap|Line Numbers
  1. ?DateDiff("m", #5/15/1982#, #12/28/2020#) \ 12
  2.  38 
  3. ?DateDiff("m", #12/31/1982#, #12/28/2020#) \ 12 
  4.  38 
3 Weeks Ago #11
ADezii
Expert 8TB
Probably just another Senior Moment, or two, on my part, but I was under the impression that your syntax would generate a Runtime Error. I thought that you could pass Date() as an Optional Parameter, but you couldn't include it within an Optional Argument as the Default Value. My apologies.
Expand|Select|Wrap|Line Numbers
  1. Public Function Age(datBirth As Date, Optional varNow As Variant) As Long
  2.   If IsMissing(varNow) Then varNow = Date
  3.  
  4.   Age = DateDiff("m", datBirth, varNow) \ 12
  5. End Function
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Age(#3/17/1960#,Date)    'My DOB
  2.  
3 Weeks Ago #12
ADezii
Expert 8TB
@isladogs:
Have you tried executing the Age() Function, and if so, are you getting an Error?
3 Weeks Ago #13
isladogs
Expert 128KB
Yes I got an error as well with the Date argument of @NeoPa's function.
I didn't spend any time trying to fix it as I knew it didn't give 100% reliable results
That's why I just used the DateDiff expression it contained which does 'work'

Just checked & your code in post #12 runs without error ... though of course it is still incorrect for anyone with a birthday after today's date
3 Weeks Ago #14
ADezii
Expert 8TB
@isladogs:
The Code in Post# 9 should cover all of the contingencies, the most important of which is exactly when the Birthday occurs in the Current Year.
3 Weeks Ago #15
ADezii
Expert 8TB
@isladogs:
The Code in Post# 9 should cover all of the contingencies, the most important of which is exactly when the Birthday occurs in the Current Year.
Just checked & yours runs without error ... though of course it is still incorrect for anyone with a birthday after today's date
I am assuming that you are referring to Post# 12. That Code was only to illustrate how the Date() Function could work in the current context, nothing more. As mentioned previously, Post# 9 should be fine (no rhyme pun intended).
3 Weeks Ago #16
isladogs
Expert 128KB
Apologies. Of course I was referring to the function in post #12. I've edited my post to say that.

I didn't try the code you gave in post #9 but both that & those in the link I gave in post #7 should all be fine

If I need age in years, I often just use:
Expand|Select|Wrap|Line Numbers
  1. DateDiff("yyyy", [DOB], Date) + (Format([DOB], "mmdd") > Format(Date, "mmdd"))
Much simpler than yours but equally valid

It gets progressively more complex if you also need months and then days as well as years
3 Weeks Ago #17
ADezii
Expert 8TB
Simpler is usually better. If I eliminate the Comments and the Optional Argument, it does get much simpler, but you won the 1st Prise for simplicity Here! (LOL)
3 Weeks Ago #18
isladogs
Expert 128KB
Ha!
If I get time, I'll find the code I've used in the past for age in years, months & days. The days part was the trickiest!
3 Weeks Ago #19
NeoPa
Expert Mod 16PB
Apologies to all. Both of you are correct of course. I did some quick testing but pretty much threw it together to fire it off before I got tied up elsewhere.

ADezii's technique for handling the default value is the way to go and I would suggest IslaDogs' simple approach for the logic makes good sense.

For my pennance I offer both your suggestions back as a usable procedure :
Expand|Select|Wrap|Line Numbers
  1. Public Function Age(datBirth As Date _
  2.                   , Optional ByVal varNow As Variant) As Long
  3.     If IsMissing(varNow) Then varNow = VBA.Date()
  4.     Age = DateDiff("yyyy", datBirth, varNow) _
  5.         - IIf(Format(varNow, "mmdd") < Format(datBirth, "mmdd"), 1, 0)
  6. End Function
NB. Though using the numerical value of True (-1) is very clever it's also quite obscure so I try to avoid where possible in working code.

Unlike my last offering this :
  1. Actually compiles.
  2. Seems to work accurately.
3 Weeks Ago #20
isladogs
Expert 128KB
OK if there is a prize for concise code for a complex task, then this is worth a shout. It calculates the date of Easter Sunday up to at least 2368.

Expand|Select|Wrap|Line Numbers
  1. Public Function GetEasterSunday(Yr As Integer) As Date
  2.  
  3. 'Based on code from http://www.cpearson.com/excel/Easter.aspx
  4.    Dim D As Integer
  5.    D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
  6.    GetEasterSunday = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + D + (D > 48) + 1) Mod 7)
  7.  
  8. End Function
As stated, it is based on code by the late, great Chip Pearson for Excel. All I did was convert it for use in Access. It works! For more info, see Easter Calculator

BTW I think we may have scared off the OP.
3 Weeks Ago #21
cactusdata
Expert 128KB
To return to the question - if you need a bullet-proof method to calculate age having two dates for, say, insurance and pension calculations, you must implement DateAdd:

Expand|Select|Wrap|Line Numbers
  1. Public Function Years( _
  2.     ByVal datDate1 As Date, _
  3.     ByVal datDate2 As Date, _
  4.     Optional ByVal booLinear As Boolean) _
  5.     As Integer
  6.  
  7. ' Returns the difference in full years between datDate1 and datDate2.
  8. '
  9. ' Calculates correctly for:
  10. '   negative differences
  11. '   leap years
  12. '   dates of 29. February
  13. '   date/time values with embedded time values
  14. '   negative date/time values (prior to 1899-12-29)
  15. '
  16. ' Optionally returns negative counts rounded down to provide a
  17. ' linear sequence of year counts.
  18. ' For a given datDate1, if datDate2 is decreased step wise one year from
  19. ' returning a positive count to returning a negative count, one or two
  20. ' occurrences of count zero will be returned.
  21. ' If booLinear is False, the sequence will be:
  22. '   3, 2, 1, 0,  0, -1, -2
  23. ' If booLinear is True, the sequence will be:
  24. '   3, 2, 1, 0, -1, -2, -3
  25. '
  26. ' If booLinear is False, reversing datDate1 and datDate2 will return
  27. ' results of same absolute Value, only the sign will change.
  28. ' This behaviour mimics that of Fix().
  29. ' If booLinear is True, reversing datDate1 and datDate2 will return
  30. ' results where the negative count is offset by -1.
  31. ' This behaviour mimics that of Int().
  32.  
  33. ' DateAdd() is used for check for month end of February as it correctly
  34. ' returns Feb. 28. when adding a count of years to dates of Feb. 29.
  35. ' when the resulting year is a common year.
  36. '
  37. ' 2007-06-22. Version 2. Complete rewrite.
  38.  
  39.     Dim intDiff   As Integer
  40.     Dim intSign   As Integer
  41.     Dim intYears  As Integer
  42.  
  43.     ' Find difference in calendar years.
  44.     intYears = DateDiff("yyyy", datDate1, datDate2)
  45.     ' For positive resp. negative intervals, check if the second date
  46.     ' falls before, on, or after the crossing date for a full 12 months period
  47.     ' while at the same time correcting for February 29. of leap years.
  48.     If DateDiff("d", datDate1, datDate2) > 0 Then
  49.         intSign = Sgn(DateDiff("d", DateAdd("yyyy", intYears, datDate1), datDate2))
  50.         intDiff = Abs(intSign < 0)
  51.     Else
  52.         intSign = Sgn(DateDiff("d", DateAdd("yyyy", -intYears, datDate2), datDate1))
  53.         If intSign <> 0 Then
  54.             ' Offset negative count of years to continuous sequence if requested.
  55.             intDiff = Abs(booLinear)
  56.         End If
  57.         intDiff = intDiff - Abs(intSign < 0)
  58.     End If
  59.  
  60.     ' Return count of years as count of full 12 months periods.
  61.     Years = intYears - intDiff
  62.  
  63. End Function
However, the questioneer seems not to have a date of birth, only the year of birth, thus the expression would be:

Expand|Select|Wrap|Line Numbers
  1. Age = Year([Consultation date]) - [Year of birth]
3 Weeks Ago #22
isladogs
Expert 128KB
@cactusdata
if you need a bullet-proof method to calculate age having two dates for, say, insurance and pension calculations, you must implement DateAdd:

I disagree.
Using DateAdd is certainly a valid approach but it is NOT ESSENTIAL to use it.

This is the expression I gave earlier written as a function

Expand|Select|Wrap|Line Numbers
  1. Function AgeYears(datDate1 As Date, datDate2 As Date) As Integer
  2.     AgeYears = DateDiff("yyyy", datDate1, datDate2) + (Format(datDate1, "mmdd") > Format(datDate2, "mmdd"))
  3. End Function
It copes perfectly with leap years and times aren't an issue.
Dates before #12/29/1899# are fine as well.
It works providing datDate1<=datDate2 (DOB as datDate1)

If you need to worry about giving positive values for age where DOB is datDate2 (datDate1 > datDate2) then this variation should cover that eventuality as well

Expand|Select|Wrap|Line Numbers
  1. Function AgeYears(datDate1 As Date, datDate2 As Date) As Integer
  2.  
  3.     If datDate1 <= datDate2 Then
  4.         AgeYears = DateDiff("yyyy", datDate1, datDate2) + (Format(datDate1, "mmdd") > Format(datDate2, "mmdd"))
  5.     Else
  6.         AgeYears = Abs(DateDiff("yyyy", datDate1, datDate2) + (Format(datDate1, "mmdd") > Format(datDate2, "mmdd"))) - 1
  7.     End If
  8. End Function
3 Weeks Ago #23
isladogs
Expert 128KB
I've just found one of the functions I have used over many years to get more precise differences between any 2 dates. AFAIK the code works perfectly for all date values. The code was written by Graham Seach back in 2002 with modifications by Doug Steele.

Expand|Select|Wrap|Line Numbers
  1. Public Function Diff2Dates(Interval As String, Date1 As Variant, Date2 As Variant, _
  2. Optional ShowZero As Boolean = False) As Variant
  3. 'Author:    ? Copyright 2001 Pacific Database Pty Limited
  4. '           Graham R Seach MCP MVP gseach@pacificdb.com.au
  5. '           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
  6. '           This code is freeware. Enjoy...
  7. '           (*) Amendments suggested by Douglas J. Steele MVP
  8. '
  9. 'Description:   This function calculates the number of years,
  10. '               months, days, hours, minutes and seconds between
  11. '               two dates, as elapsed time.
  12. '
  13. 'Inputs:    Interval:   Intervals to be displayed (a string)
  14. '           Date1:      The lower date (see below)
  15. '           Date2:      The higher date (see below)
  16. '           ShowZero:   Boolean to select showing zero elements
  17. '
  18. 'Outputs:   On error: Null
  19. '           On no error: Variant containing the number of years,
  20. '               months, days, hours, minutes & seconds between
  21. '               the two dates, depending on the display interval
  22. '               selected.
  23. '           If Date1 is greater than Date2, the result will
  24. '               be a negative value.
  25. '           The function compensates for the lack of any intervals
  26. '               not listed. For example, if Interval lists "m", but
  27. '               not "y", the function adds the value of the year
  28. '               component to the month component.
  29. '           If ShowZero is True, and an output element is zero, it
  30. '               is displayed. However, if ShowZero is False or
  31. '               omitted, no zero-value elements are displayed.
  32. '               For example, with ShowZero = False, Interval = "ym",
  33. '               elements = 0 & 1 respectively, the output string
  34. '               will be "1 month" - not "0 years 1 month".
  35.  
  36. On Error GoTo Err_Diff2Dates
  37.  
  38.    Dim booCalcYears As Boolean
  39.    Dim booCalcMonths As Boolean
  40.    Dim booCalcDays As Boolean
  41.    Dim booCalcHours As Boolean
  42.    Dim booCalcMinutes As Boolean
  43.    Dim booCalcSeconds As Boolean
  44.    Dim booCalcWeeks As Boolean
  45.    Dim booSwapped As Boolean
  46.    Dim dtTemp As Date
  47.    Dim intCounter As Integer
  48.    Dim lngDiffYears As Long
  49.    Dim lngDiffMonths As Long
  50.    Dim lngDiffDays As Long
  51.    Dim lngDiffHours As Long
  52.    Dim lngDiffMinutes As Long
  53.    Dim lngDiffSeconds As Long
  54.    Dim lngDiffWeeks As Long
  55.    Dim varTemp As Variant
  56.  
  57.    Const INTERVALS As String = "dmyhnsw"
  58.  
  59. 'Check that Interval contains only valid characters
  60.    Interval = LCase$(Interval)
  61.    For intCounter = 1 To Len(Interval)
  62.       If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
  63.          Exit Function
  64.       End If
  65.    Next intCounter
  66.  
  67. 'Check that valid dates have been entered
  68.    If IsNull(Date1) Then Exit Function
  69.    If IsNull(Date2) Then Exit Function
  70.    If Not (IsDate(Date1)) Then Exit Function
  71.    If Not (IsDate(Date2)) Then Exit Function
  72.  
  73. 'If necessary, swap the dates, to ensure that
  74. 'Date1 is lower than Date2
  75.    If Date1 > Date2 Then
  76.       dtTemp = Date1
  77.       Date1 = Date2
  78.       Date2 = dtTemp
  79.       booSwapped = True
  80.    End If
  81.  
  82.    Diff2Dates = Null
  83.    varTemp = Null
  84.  
  85. 'What intervals are supplied
  86.    booCalcYears = (InStr(1, Interval, "y") > 0)
  87.    booCalcMonths = (InStr(1, Interval, "m") > 0)
  88.    booCalcDays = (InStr(1, Interval, "d") > 0)
  89.    booCalcHours = (InStr(1, Interval, "h") > 0)
  90.    booCalcMinutes = (InStr(1, Interval, "n") > 0)
  91.    booCalcSeconds = (InStr(1, Interval, "s") > 0)
  92.    booCalcWeeks = (InStr(1, Interval, "w") > 0)
  93.  
  94. 'Get the cumulative differences
  95.    If booCalcYears Then
  96.       lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
  97.               IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
  98.       Date1 = DateAdd("yyyy", lngDiffYears, Date1)
  99.    End If
  100.  
  101.    If booCalcMonths Then
  102.       lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
  103.               IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
  104.       Date1 = DateAdd("m", lngDiffMonths, Date1)
  105.    End If
  106.  
  107.    If booCalcWeeks Then
  108.       lngDiffWeeks = Abs(DateDiff("w", Date1, Date2)) - _
  109.               IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
  110.       Date1 = DateAdd("ww", lngDiffWeeks, Date1)
  111.    End If
  112.  
  113.    If booCalcDays Then
  114.       lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
  115.               IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
  116.       Date1 = DateAdd("d", lngDiffDays, Date1)
  117.    End If
  118.  
  119.    If booCalcHours Then
  120.       lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
  121.               IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
  122.       Date1 = DateAdd("h", lngDiffHours, Date1)
  123.    End If
  124.  
  125.    If booCalcMinutes Then
  126.       lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
  127.               IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
  128.       Date1 = DateAdd("n", lngDiffMinutes, Date1)
  129.    End If
  130.  
  131.    If booCalcSeconds Then
  132.       lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
  133.       Date1 = DateAdd("s", lngDiffSeconds, Date1)
  134.    End If
  135.  
  136.    If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
  137.       varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
  138.    End If
  139.  
  140.    If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
  141.       If booCalcMonths Then
  142.          varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
  143.                    lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
  144.       End If
  145.    End If
  146.  
  147.    If booCalcWeeks And (lngDiffWeeks > 0 Or ShowZero) Then
  148.       If booCalcWeeks Then
  149.          varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
  150.                    lngDiffWeeks & IIf(lngDiffWeeks <> 1, " weeks", " week")
  151.       End If
  152.    End If
  153.  
  154.    If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
  155.       If booCalcDays Then
  156.          varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
  157.                    lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
  158.       End If
  159.    End If
  160.  
  161.    If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
  162.       If booCalcHours Then
  163.          varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
  164.                    lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
  165.       End If
  166.    End If
  167.  
  168.    If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
  169.       If booCalcMinutes Then
  170.          varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
  171.                    lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
  172.       End If
  173.    End If
  174.  
  175.    If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
  176.       If booCalcSeconds Then
  177.          varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
  178.                    lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
  179.       End If
  180.    End If
  181.  
  182.    If booSwapped Then
  183.       varTemp = "-" & varTemp
  184.    End If
  185.  
  186.    Diff2Dates = Trim$(varTemp)
  187.  
  188. End_Diff2Dates:
  189.    Exit Function
  190.  
  191. Err_Diff2Dates:
  192.    Resume End_Diff2Dates
  193.  
  194. End Function
Examples of usage:
Expand|Select|Wrap|Line Numbers
  1. ?Diff2Dates("y",#5/15/52#,#12/29/2020#)
  2. 68 years
  3.  
  4. ?Diff2Dates("y",#5/15/1952#,Date())
  5. 68 years
  6.  
  7. ?Diff2Dates("y",Date(), #5/15/1952#)
  8. -68 years
  9.  
  10. ?Diff2Dates("ymd",#5/15/52#,#12/29/2020#)
  11. 68 years 7 months 14 days
  12.  
  13. ?Diff2Dates("ymd",#12/29/2020#, #5/15/52#)
  14. -68 years 7 months 14 days
  15.  
  16. ?Diff2Dates("ymdhns",#5/15/52 07:25:11#,#12/29/2020 14:45:22#)
  17. 68 years 7 months 14 days 7 hours 20 minutes 11 seconds
  18.  
  19. ?Diff2Dates("ys",#5/15/52 07:25:11#,#12/29/2020 14:45:22#)
  20. 68 years 19725611 seconds
  21.  
  22. ?Diff2Dates("ymds",#5/15/52 07:25:11#,#12/29/2020 14:45:22#)
  23. 68 years 7 months 14 days 26411 seconds
  24.  
  25. ?Diff2Dates("ymd",#2/29/1892#, #3/1/1897#)
  26. 5 years 1 day
3 Weeks Ago #24
cactusdata
Expert 128KB
Yes, there are many variations of this and other methods as well, but they fail for the leaplings. Only DateAdd does it right for any date.

And yes, they are a minority - assuming an even distribution of births per day, they count for less than 0.07% of the population - but still.
3 Weeks Ago #25
isladogs
Expert 128KB
I'm sorry but in my opinion that is nonsense.
Both my code in post #23 and that in post #24 work perfectly where leap years are involved in one or both dates as I said in previous posts.
I suggest you actually try both sets of code and check the results. If you can find examples to disprove my comment, I will apologise unreservedly.

Also if you check the link I gave in post #4, many highly respected developers including Allen Browne & Daniel Pineault have also posted functions using DateDiff to calculate age correctly.

I'll repeat that using DateAdd is perfectly valid but it is not the only 100% reliable code for this purpose.

Further examples with leap dates using the function in post #23
Expand|Select|Wrap|Line Numbers
  1. ?AgeYears(#2/29/1952#, #2/28/2020#)
  2.  67 
  3. ?AgeYears(#2/29/1952#, #2/29/2020#)
  4.  68 
  5. ?AgeYears(#3/1/1952#, #2/29/2020#)
  6.  67 
  7. ?AgeYears(#2/29/1952#, #3/1/2020#)
  8.  68 
  9. ?AgeYears(#2/28/1952#, #2/29/2020#)
  10.  68 
  11. ?AgeYears(#2/29/1952#, #2/29/2000#)
  12.  48 
  13. ?AgeYears(#2/29/1852#, #2/28/1900#)
  14.  47 
  15. ?AgeYears(#2/29/2000#, #2/28/2100#)
  16.  99 
  17.  
  18.  
3 Weeks Ago #26
ADezii
Expert 8TB
@isladogs:
As you stated previously in Post# 21, I do believe that we scared off the OP! (LOL).
3 Weeks Ago #27
cactusdata
Expert 128KB
But you forgot a non-trivial example like:

Expand|Select|Wrap|Line Numbers
  1. ?AgeYears(#2/29/1952#, #2/28/2019#)
  2.  66 
  3.  
  4. ?Years(#2/29/1952#, #2/28/2019#)
  5.  67 
And the last two should return 48 and 100 respectively:

Expand|Select|Wrap|Line Numbers
  1. ?Years(#2/29/1852#, #2/28/1900#)
  2.  48 
  3.  
  4. ?Years(#2/29/2000#, #2/28/2100#)
  5.  100 
3 Weeks Ago #28
isladogs
Expert 128KB
Sorry but I disagree.
To be pedantic, someone doesn't reach their birthday until the actual date arrives. Someone born on 29 Feb hasn't reached their birth date on 28 Feb whether or not it is a leap year. The person may choose to celebrate that birthday on 28 Feb or on 1 Mar but either date would be down to their choice. See also Leap Day Timeline
So I maintain that all my examples were correct and for the same reason that your interpretation is, strictly speaking, incorrect.

And finally, another example using Graham Seach's code in post #24

Expand|Select|Wrap|Line Numbers
  1. ?Diff2Dates("ymd",#2/29/2000#,#2/28/2005#)
  2. 4 years 11 months 30 days
  3.  
3 Weeks Ago #29
cactusdata
Expert 128KB
If you are born on the last day of February, the age is, logically, counted on the last day of February, not in the next month. You may, of course, celebrate it on any other day as to your preferences.

Also, you should trash that old code, as February never reaches 30 days.
Should you one day wish to present a trustworthy result, I have a function for this purpose:

Expand|Select|Wrap|Line Numbers
  1. ? FormatAgeYearsMonthsDays(#2/29/2000#,#2/28/2005#)
  2. 5 years, 0 months, 0 days
3 Weeks Ago #30
isladogs
Expert 128KB
We will have to agree to disagree.
The age/birthday is logically counted when people choose to do so. As the article I linked makes clear, the legal date for leaplings to be considered an adult varies according to the country: 28 Feb in NZ, 1 Mar in the UK. Both are equally valid decisions made.

Thanks for the offer but I have code that, as far as I and many others are concerned, gives perfectly satisfactory results.
3 Weeks Ago #31
NeoPa
Expert Mod 16PB
I guess that, before we argue practically & effectively on whether or not any particular set of code correctly handles age calculations, we need to discuss (decide/argue/etc) exactly what we understand by the term 'age' in this context.

I suspect both sides of the argument are correct as far as their own determination of the meaning goes.

As far as the thread goes, while we may all have ventured a little way off-topic, I'm happy to allow this as the discussion here has been very illuminative and gives readers a valuable in-depth illustration of some of the complications of 'age' calculation (in their varying forms). So, please, continue to disagree (in as friendly a manner as you can) and contribute your valuable thoughts and work to the discussions.

My thanks to all involved.
3 Weeks Ago #32
ADezii
Expert 8TB
@NeoPa:
You are a natural referee! (LOL). As me me, I`m staying out of the ring.
3 Weeks Ago #33
isladogs
Expert 128KB
I do apologise. I got a bit carried away arguing my case
3 Weeks Ago #34
NeoPa
Expert Mod 16PB
Cheers ADezii.

As the administrator here it sort of goes with the job, but I have a natural advantage anyway - I'm more argumentative than most.

Also, when you aren't directly involved it's easier to see the merit of both sides - and let's be fair - they both added considerably to the topic.
3 Weeks Ago #35

Post your reply

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

Similar topics

reply views Thread by Mark Phanides | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.