Calculating Age based on Date of Admission 1

[gRegulator]

Hi,

I am trying to calculate the age that a client would be at the age of admission into our organization.

I know the current age would be found by:
(Date()-[Date of Birth])/365.25 AS [Age At Admission])

There are two ways by which a person may enter our organization, either Probation [Probation Start] or Conditional Sentence [CS Start]. So I know how to calculate the age when they enter for either one or the other, but i just dont know how to write it so that if the [Probation Start] is blank the age will be calculated from the [CS Start] and vice versa. Here is my current coding for the entire query:

SELECT tbl_offenders.[Offender #], tbl_offenders.[Given Names], tbl_offenders.Surname, (Date()-[Date of Birth])/365.25 AS [Age At Admission], AgeGp([Age At Admission]) AS [Age Group], tbl_offenders.Probation, tbl_offenders.[Community Service Order], tbl_offenders.[Conditional Sentence], tbl_offenders.[Fine Option Program], tbl_offenders.[CS Start], tbl_offenders.[Probation Start]
FROM tbl_offenders;

So is there any way to write it so that instead of calculating the current age, or just from either the [CS Start] or [Prob Start] Fields.

I should also note that [Prob Start] will only have a value if Probation = True, [CS Start] will only have a value if Conditional Sentence = True.

I hope this makes sense. Thanks in advance!

 

[PHV]

(Nz([Probation Start],[CS Start])-[Date of Birth])/365.25 AS [Age At Admission],

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gRegulator]

Worked like a charm! Thanks!

 

[MichaelRed]

but ... but ... but ...

you REAllY should check some several of the numerous threads re calculating "age" ... some ... many ... most(?) will agree that your formula is not the better way ...





MichaelRed

 

[r937]

that's right, dividing the date difference by 365.25 will not give the right answer in all circumstances

that's because if your birthday is on june 15th, you don't add 1 to your age until that specific day of the year, no matter how you round up or round down the result of the division

plenty of previous threads on tek-tips (if not in this specific forum) on that topic



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)