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

[shannel]

the expression is
age(Year [Consultation date]-[Year of birth])
please help

[NeoPa]

Don't overlook the parentheses ;-)

Age() and Year() functions both use them.

[isladogs]

Also, do you have your own Age function as there isn't one built into Access

[twinnyfo]

My initial stab is that if Age is a variable, the following should work:

Age = Year([Consultation date]-[Year of birth])

But, like others, this is simply a guess as to what OP is after.

[isladogs]

@twinnyfo:
Bracketing.... I expect you meant:

Age = Year([Consultation date])-[Year of birth]

Of course that is only accurate if the patient has already had their birthday this year.

[twinnyfo]

Isladogs,

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

Good catch!

Your solution should definitely work!

[isladogs]

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

[twinnyfo]

Agreed - comparing (or adding/subtracting) dates with years just causes problems from the start.

[ADezii]

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:

Function fCalculateAge(varDOB As Variant, Optional varAsOfThisDate As Variant) As Variant
'Purpose:   Return the Age in years.
'Arguments: varDOB = Date Of Birth
'varAsOfThisDate = the date to calculate the age at, or today if missing.
 
'The DateDiff() Function simply subtracts the Year parts of the Dates, without
'reference to the Month or Day. This means we need to subtract one if the person
'has not has their birthday this year. This can be handled by a Boolean Expression
'that returns either -1 or 0 depending on whether or not an individual's
'birthday occurred within the Year.
 
'Return:    Whole number of years.
Dim dteDOB As Date
Dim dteAsOf As Date
Dim dteBDay As Date     'Birthday in the year of calculation.
 
fCalculateAge = Null    'Initialize to Null
 
'Validate Parameters
If IsDate(varDOB) Then
  dteDOB = varDOB
 
  If Not IsDate(varAsOfThisDate) Then  'Date to calculate age from.
    dteAsOf = Date      'Use the Current Date
  Else
    dteAsOf = varAsOfThisDate       'Valid As Of Date
  End If
 
  If dteAsOf >= dteDOB Then      'Calculate only if it's after person was born.
    dteBDay = DateSerial(Year(dteAsOf), Month(dteDOB), Day(dteDOB))
      fCalculateAge = DateDiff("yyyy", dteDOB, dteAsOf) + (dteBDay > dteAsOf)
  End If
End If
End Function

[NeoPa]

Am I missing something? Calculating age in the normal way is surely as simple as :

Public Function Age(datBirth As Date _
                  , Optional ByVal datNow As Date=Date()) As Long
    Age = DateDiff("m", datBirth, datNow) \ 12
End Function

[isladogs]

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

?DateDiff("m", #5/15/1982#, #12/28/2020#) \ 12
 38 
?DateDiff("m", #12/31/1982#, #12/28/2020#) \ 12 
 38 

[ADezii]

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.

Public Function Age(datBirth As Date, Optional varNow As Variant) As Long
  If IsMissing(varNow) Then varNow = Date
 
  Age = DateDiff("m", datBirth, varNow) \ 12
End Function

Debug.Print Age(#3/17/1960#,Date)    'My DOB
 

[ADezii]

@isladogs:
Have you tried executing the Age() Function, and if so, are you getting an Error?

[isladogs]

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

[ADezii]

@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.

[ADezii]

@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).

[isladogs]

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:

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

[ADezii]

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)

[isladogs]

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!

[NeoPa]

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 :

Public Function Age(datBirth As Date _
                  , Optional ByVal varNow As Variant) As Long
    If IsMissing(varNow) Then varNow = VBA.Date()
    Age = DateDiff("yyyy", datBirth, varNow) _
        - IIf(Format(varNow, "mmdd") < Format(datBirth, "mmdd"), 1, 0)
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.

[isladogs]

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.

Public Function GetEasterSunday(Yr As Integer) As Date
 
'Based on code from http://www.cpearson.com/excel/Easter.aspx
   Dim D As Integer
   D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
   GetEasterSunday = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + D + (D > 48) + 1) Mod 7)
 
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.

[cactusdata]

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:

Public Function Years( _
    ByVal datDate1 As Date, _
    ByVal datDate2 As Date, _
    Optional ByVal booLinear As Boolean) _
    As Integer
 
' Returns the difference in full years between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of year counts.
' For a given datDate1, if datDate2 is decreased step wise one year from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().
 
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2007-06-22. Version 2. Complete rewrite.
 
    Dim intDiff   As Integer
    Dim intSign   As Integer
    Dim intYears  As Integer
 
    ' Find difference in calendar years.
    intYears = DateDiff("yyyy", datDate1, datDate2)
    ' For positive resp. negative intervals, check if the second date
    ' falls before, on, or after the crossing date for a full 12 months period
    ' while at the same time correcting for February 29. of leap years.
    If DateDiff("d", datDate1, datDate2) > 0 Then
        intSign = Sgn(DateDiff("d", DateAdd("yyyy", intYears, datDate1), datDate2))
        intDiff = Abs(intSign < 0)
    Else
        intSign = Sgn(DateDiff("d", DateAdd("yyyy", -intYears, datDate2), datDate1))
        If intSign <> 0 Then
            ' Offset negative count of years to continuous sequence if requested.
            intDiff = Abs(booLinear)
        End If
        intDiff = intDiff - Abs(intSign < 0)
    End If
 
    ' Return count of years as count of full 12 months periods.
    Years = intYears - intDiff
 
End Function

However, the questioneer seems not to have a date of birth, only the year of birth, thus the expression would be:

Age = Year([Consultation date]) - [Year of birth]

[isladogs]

@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

Function AgeYears(datDate1 As Date, datDate2 As Date) As Integer
    AgeYears = DateDiff("yyyy", datDate1, datDate2) + (Format(datDate1, "mmdd") > Format(datDate2, "mmdd"))
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

Function AgeYears(datDate1 As Date, datDate2 As Date) As Integer
 
    If datDate1 <= datDate2 Then
        AgeYears = DateDiff("yyyy", datDate1, datDate2) + (Format(datDate1, "mmdd") > Format(datDate2, "mmdd"))
    Else
        AgeYears = Abs(DateDiff("yyyy", datDate1, datDate2) + (Format(datDate1, "mmdd") > Format(datDate2, "mmdd"))) - 1
    End If
End Function

[isladogs]

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.

Public Function Diff2Dates(Interval As String, Date1 As Variant, Date2 As Variant, _
Optional ShowZero As Boolean = False) As Variant
'Author:    ? Copyright 2001 Pacific Database Pty Limited
'           Graham R Seach MCP MVP [e-mail]
'           Phone: [phone]  Fax: [phone]
'           This code is freeware. Enjoy...
'           (*) Amendments suggested by Douglas J. Steele MVP
'
'Description:   This function calculates the number of years,
'               months, days, hours, minutes and seconds between
'               two dates, as elapsed time.
'
'Inputs:    Interval:   Intervals to be displayed (a string)
'           Date1:      The lower date (see below)
'           Date2:      The higher date (see below)
'           ShowZero:   Boolean to select showing zero elements
'
'Outputs:   On error: Null
'           On no error: Variant containing the number of years,
'               months, days, hours, minutes & seconds between
'               the two dates, depending on the display interval
'               selected.
'           If Date1 is greater than Date2, the result will
'               be a negative value.
'           The function compensates for the lack of any intervals
'               not listed. For example, if Interval lists "m", but
'               not "y", the function adds the value of the year
'               component to the month component.
'           If ShowZero is True, and an output element is zero, it
'               is displayed. However, if ShowZero is False or
'               omitted, no zero-value elements are displayed.
'               For example, with ShowZero = False, Interval = "ym",
'               elements = 0 & 1 respectively, the output string
'               will be "1 month" - not "0 years 1 month".
 
On Error GoTo Err_Diff2Dates
 
   Dim booCalcYears As Boolean
   Dim booCalcMonths As Boolean
   Dim booCalcDays As Boolean
   Dim booCalcHours As Boolean
   Dim booCalcMinutes As Boolean
   Dim booCalcSeconds As Boolean
   Dim booCalcWeeks As Boolean
   Dim booSwapped As Boolean
   Dim dtTemp As Date
   Dim intCounter As Integer
   Dim lngDiffYears As Long
   Dim lngDiffMonths As Long
   Dim lngDiffDays As Long
   Dim lngDiffHours As Long
   Dim lngDiffMinutes As Long
   Dim lngDiffSeconds As Long
   Dim lngDiffWeeks As Long
   Dim varTemp As Variant
 
   Const INTERVALS As String = "dmyhnsw"
 
'Check that Interval contains only valid characters
   Interval = LCase$(Interval)
   For intCounter = 1 To Len(Interval)
      If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
         Exit Function
      End If
   Next intCounter
 
'Check that valid dates have been entered
   If IsNull(Date1) Then Exit Function
   If IsNull(Date2) Then Exit Function
   If Not (IsDate(Date1)) Then Exit Function
   If Not (IsDate(Date2)) Then Exit Function
 
'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
   If Date1 > Date2 Then
      dtTemp = Date1
      Date1 = Date2
      Date2 = dtTemp
      booSwapped = True
   End If
 
   Diff2Dates = Null
   varTemp = Null
 
'What intervals are supplied
   booCalcYears = (InStr(1, Interval, "y") > 0)
   booCalcMonths = (InStr(1, Interval, "m") > 0)
   booCalcDays = (InStr(1, Interval, "d") > 0)
   booCalcHours = (InStr(1, Interval, "h") > 0)
   booCalcMinutes = (InStr(1, Interval, "n") > 0)
   booCalcSeconds = (InStr(1, Interval, "s") > 0)
   booCalcWeeks = (InStr(1, Interval, "w") > 0)
 
'Get the cumulative differences
   If booCalcYears Then
      lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
              IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
      Date1 = DateAdd("yyyy", lngDiffYears, Date1)
   End If
 
   If booCalcMonths Then
      lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
              IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
      Date1 = DateAdd("m", lngDiffMonths, Date1)
   End If
 
   If booCalcWeeks Then
      lngDiffWeeks = Abs(DateDiff("w", Date1, Date2)) - _
              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
      Date1 = DateAdd("ww", lngDiffWeeks, Date1)
   End If
 
   If booCalcDays Then
      lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
      Date1 = DateAdd("d", lngDiffDays, Date1)
   End If
 
   If booCalcHours Then
      lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
              IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
      Date1 = DateAdd("h", lngDiffHours, Date1)
   End If
 
   If booCalcMinutes Then
      lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
              IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
      Date1 = DateAdd("n", lngDiffMinutes, Date1)
   End If
 
   If booCalcSeconds Then
      lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
      Date1 = DateAdd("s", lngDiffSeconds, Date1)
   End If
 
   If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
      varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
   End If
 
   If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
      If booCalcMonths Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
      End If
   End If
 
   If booCalcWeeks And (lngDiffWeeks > 0 Or ShowZero) Then
      If booCalcWeeks Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffWeeks & IIf(lngDiffWeeks <> 1, " weeks", " week")
      End If
   End If
 
   If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
      If booCalcDays Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
      End If
   End If
 
   If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
      If booCalcHours Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
      End If
   End If
 
   If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
      If booCalcMinutes Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
      End If
   End If
 
   If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
      If booCalcSeconds Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
      End If
   End If
 
   If booSwapped Then
      varTemp = "-" & varTemp
   End If
 
   Diff2Dates = Trim$(varTemp)
 
End_Diff2Dates:
   Exit Function
 
Err_Diff2Dates:
   Resume End_Diff2Dates
 
End Function

Examples of usage:

?Diff2Dates("y",#5/15/52#,#12/29/2020#)
68 years
 
?Diff2Dates("y",#5/15/1952#,Date())
68 years
 
?Diff2Dates("y",Date(), #5/15/1952#)
-68 years
 
?Diff2Dates("ymd",#5/15/52#,#12/29/2020#)
68 years 7 months 14 days
 
?Diff2Dates("ymd",#12/29/2020#, #5/15/52#)
-68 years 7 months 14 days
 
?Diff2Dates("ymdhns",#5/15/52 07:25:11#,#12/29/2020 14:45:22#)
68 years 7 months 14 days 7 hours 20 minutes 11 seconds
 
?Diff2Dates("ys",#5/15/52 07:25:11#,#12/29/2020 14:45:22#)
68 years 19725611 seconds
 
?Diff2Dates("ymds",#5/15/52 07:25:11#,#12/29/2020 14:45:22#)
68 years 7 months 14 days 26411 seconds
 
?Diff2Dates("ymd",#2/29/1892#, #3/1/1897#)
5 years 1 day

[cactusdata]

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.

[isladogs]

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

?AgeYears(#2/29/1952#, #2/28/2020#)
 67 
?AgeYears(#2/29/1952#, #2/29/2020#)
 68 
?AgeYears(#3/1/1952#, #2/29/2020#)
 67 
?AgeYears(#2/29/1952#, #3/1/2020#)
 68 
?AgeYears(#2/28/1952#, #2/29/2020#)
 68 
?AgeYears(#2/29/1952#, #2/29/2000#)
 48 
?AgeYears(#2/29/1852#, #2/28/1900#)
 47 
?AgeYears(#2/29/2000#, #2/28/2100#)
 99 
 
 

[ADezii]

@isladogs:
As you stated previously in Post# 21, I do believe that we scared off the OP! (LOL).

[cactusdata]

But you forgot a non-trivial example like:

?AgeYears(#2/29/1952#, #2/28/2019#)
 66 
 
?Years(#2/29/1952#, #2/28/2019#)
 67 

And the last two should return 48 and 100 respectively:

?Years(#2/29/1852#, #2/28/1900#)
 48 
 
?Years(#2/29/2000#, #2/28/2100#)
 100 

[isladogs]

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

?Diff2Dates("ymd",#2/29/2000#,#2/28/2005#)
4 years 11 months 30 days
 

[cactusdata]

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:

? FormatAgeYearsMonthsDays(#2/29/2000#,#2/28/2005#)
5 years, 0 months, 0 days

[isladogs]

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.

[NeoPa]

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.

[ADezii]

@NeoPa:
You are a natural referee! (LOL). As me me, I`m staying out of the ring.

[isladogs]

I do apologise. I got a bit carried away arguing my case

[NeoPa]

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.