Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: query age groups not working?

    Hi,
    i am trying to run a query which will manipulate clients date of birth and return number of clients within an inputted age range, i have used the same function and query design in another database and it works, but it will not work in the current database that i am working on - the error message is "The Expression is typed incorrectly, or it is too complex to be evaluated"

    to get this info i have used the following function and query:

    Public Function CalcAge(dDOB As Date) As Integer

    'Objective: Given a date of birth in date format, calculate the persons age in years
    ' If MMDD for the system date >= MMDD for the DoB, then Age = Year(System Date) - Year(DoB)
    ' If MMDD for the system date < MMDD for the DoB, then Age = Year(System Date) - Year(DoB) - 1

    Dim iDobMthDay As Integer, iSysMthDay As Integer

    iDobMthDay = Month(dDOB) * 100 + Day(dDOB)
    iSysMthDay = Month(Date) * 100 + Day(Date)

    If iSysMthDay >= iDobMthDay Then
    CalcAge = Year(Date) - Year(dDOB)
    Else
    CalcAge = Year(Date) - Year(dDOB) - 1
    End If


    End Function

    query is:

    SELECT [DATA SHEET].Client_Ref_No, CalcAge([Date_of_Birth]) AS Age
    FROM [DATA SHEET]
    GROUP BY [DATA SHEET].Client_Ref_No, CalcAge([Date_of_Birth])
    HAVING (((CalcAge([Date_of_Birth]))>=[enter start age] And (CalcAge([Date_of_Birth]))<=[enter end age]));


    any help/advice will be much appreciated!!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't you take a look at the DateDiff() function in the help files?
    Code:
    Dim age As Integer
    
    age = DateDiff("yy", StartDate, EndDate)
    Code:
    SELECT DateDiff(yy, BirthDate, Now())
    FROM   myTable
    WHERE  DateDiff(yy, BirthDate, Now()) BETWEEN [enter lower age limit]
           AND  [enter upper age limit]
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    thanks,
    it returns the following error message
    "compile error, in query expression 'DateDiff(yy,Birthdate,Now())'

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try wrapping the yy in double quotes
    Code:
    "yy"
    An remember to replace BirthDate with your relevant field in the query
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2006
    Posts
    65
    Hi,
    no joy, still the same error message

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post the entire block of code please
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2006
    Posts
    65
    Hi, as requested:

    SELECT DateDiff(yy,Date_of_Birth,Now())
    FROM [DATA SHEET]
    WHERE DateDiff(yy, Date_of_Birth, Now()) BETWEEN [enter lower age limit]
    AND [enter upper age limit]

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you haven't wrapped the yy in double quotes as suggested in post #4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2006
    Posts
    65
    Hi,
    I have tried with the wraps and without, no difference, even single quotes!

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please post the query that you think should actually work that you tried
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •