Sql Code Help Needed!

[deanwat]

I have a form that contains DOB I have got my sql code to figure age by DOB for a query, but now I also need to figure youth(10-18, Adult1(19-25), Adult2(26-50) and Adult3(51 & older)From DOB or my new query field Age.
Here is the code I have so far:
SELECT tblCustomer.IdNumber, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.DOB, DATE(), (YEAR(DATE())-YEAR(DOB))-(RIGHT(DATE(),5)<RIGHT(DOB,5)) AS Age
FROM tblCustomer;

Please Help someone.

 

[SkipVought]

Consider making a table for your ranges.
[tt]
MinAge
Category
[/tt]


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[r937]

select iif(Age between 10 and 18, 'Youth',
       iif(Age between 19 and 25, 'Adult1',
       iif(Age between 26 and 50, 'Adult2',
       iif(Age >= 51, 'Adult3','unknown')))) as bucket
     , d.*
  from (
       SELECT IdNumber
            , FirstName
            , LastName
            , DOB
            , DATE()
            , YEAR(DATE())-
              YEAR(DOB)-
              RIGHT(DATE(),5)<RIGHT(DOB,5) AS Age
         FROM tblCustomer
       ) as d

r937.com | rudy.ca

 

[dhookom]

I'm with Skip on this one. Age range definitions are a "business rules" type calculation that shouldn't be an expression in a query. If/when age range definitions change, you should change data, not complex expressions in a query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]