All-inclusive age calculation statement 1

[Dbyte]

Okay, so I read the FAQs on how to calculate age using a person's DOB. I also found an If/then statement to correct the ages of those born before 1930 (removes negative age values). What I need is 1 SQL statement that combines these 2 things. Here is my existing statement that calculates age (it's part of a LONG statement pulling many values from an existing table into a new table based on a date range selected by the user in a form):

(DateDiff(&quot;yyyy&quot;,([Date of Birth]),Date())+(Date()<DateSerial(Year(Date()),Month([Date of Birth]),Day([Date of Birth])))) AS Age INTO [Report Table]

How do I add the part about correcting the age of anyone born before 1930? Can it be done as part of this statement? Or do I need to run a 2nd query to update the Age value (in the new table) based on the following criteria:

If Age>=0 then Age, Else Age=100-ABS(Age)

Thanks in advance for any help.

 

[wemeier]

You could do this in an immediate If (IIF) statement by doing the following:

IIF((DateDiff(&quot;yyyy&quot;,([Date of Birth]),Date())+(Date()<DateSerial(Year(Date()),Month([Date of Birth]),Day([Date of Birth])))) < 0, (DateDiff(&quot;yyyy&quot;,([Date of Birth]),Date())+(Date()<DateSerial(Year(Date()),Month([Date of Birth]),Day([Date of Birth])))), 100 - Abs((DateDiff(&quot;yyyy&quot;,([Date of Birth]),Date())+(Date()<DateSerial(Year(Date()),Month([Date of Birth]),Day([Date of Birth])))))) AS Age INTO [Report Table]

I've colored the code to show you that it involves taking your code and repeating it three times -- once for the IF part of the calculation, one for the TRUE result and once for the FALSE result.

I know that this looks extremely inefficient in that it does the age calculation twice for every record -- once to determine if the age will be less than zero and once to create the correct age. However, it's probably still more efficient than running a second query because a second query would involve re-reading every row whereas running the calculation twice just eats up CPU cycles and some memory.

 

[Dbyte]

That's great - thanks so much. I'll try it first thing tomorrow. You know, this would make for a good all-inclusive FAQ for calculating age.

 

[raskew]

There's no need to go through all those gyrations.

Try copying/pasting the following to a new module
then, from the debug window, type:

? agecount2(&quot;10/30/1902&quot;, date())
99

? agecount2(&quot;10/30/1492&quot;, date())
509

? agecount2(&quot;10/30/0601&quot;, date())
1400

? agecount2(&quot;9/30/0601&quot;, date())
1401

? agecount2(&quot;1/1/0100&quot;, date())
1902

It does break-down if DOB < 100 AD

? agecount2(&quot;12/31/0099&quot;, date())
2

I'll leave it to you to figure out why.

Function agecount2(pdob As Variant, pdte As Variant) As Integer
'allows entry of strings ("10/21/86") or dates (#10/21/86#)
Dim dDOB As Date, dDte As Date
dDOB = DateValue(pdob)
dDte = DateValue(pdte)
agecount2 = DateDiff(&quot;yyyy&quot;, dDOB, dDte) + (dDte < DateSerial(Year(dDte), Month(dDOB), Day(pdob)))
End Function

 

[Dbyte]

Unfortunately neither of these things is working for me. The IIF statement gave me the same result - negative age values. Using a module might be a better way to do this, but I have NO idea how to use modules in any way. So here is my original SQL statement, in its entirety:

SELECT [Intake Date], [Client #], [If yes, provide coverage], [DTA Case], [If Yes, Provide Date Closed], [DSS Case], [If Yes Provide Date Closed], [Child Does Homework], [Parent Reports Positive Discipline], [Client/Case Manager Contact Initiated], [Client Ability To Take Action], [Short-Term Goals Identified], [Short-Term Goals Met], [Benefits Reported], [School/Training Began], [School/Training Completed], [Counseling Began], [Alternative Housing Obtained], [Employed], [Still Employed at 3 Months], [Positive Problem Solving Skills], [Sobriety Maintained], [Children Increase Skills], [Intake Initials], [Data Entry Date], [Data Entry Initials], [Admit Date], [ID Number], [Date of Birth], [Social Security Number], [Alien Registration Number], [Last Name], [First Name], [Suffix], [Middle Initial], [Apartment/House Number], [Street Name], [City], [State], [Zip], [Home Phone], [Work Phone], [Emergency Contact Name], [Emergency Contact Address], [Relationship], [Emergency Contact Home Phone], [Emergency Contact Work Phone], [Refered By/How Hear of Us?], [Reason for Client Visit], [Gender], [Race], [Family Status], [Family Size], [Client Disability], [If Yes, Select Code], [Child Support], [Annual Gross Household Income], [Annual Net Household Income], [Low Income], [Low/Moderate Income], [Income Sources], [Housing Status], [Housing Type], [Education Status], [Single Household], [Teen Parent (Ever)], [Food Stamps], [WIC], [Medicare], [Mass Health], [Veteran], [Not a Veteran], [Farmer], [Migrant Farmer], [Seasonal Farmer], [Preferred language for Communication], [Kennedy Center Program ID], [Annual Income], [Insurance], [Sex], [Disability Type], (DateDiff(&quot;yyyy&quot;,([Date of Birth]),Date())+(Date()<DateSerial(Year(Date()),Month([Date of Birth]),Day([Date of Birth])))) AS Age INTO [Report Table]
FROM [Client Information Table]
WHERE ((([Intake Date])>=[Forms]![Dates]![startdate] And ([Intake Date])<=[Forms]![Dates]![enddate]));

The part I need help with starts with 'DateDiff', & is 6 lines up from the bottom of the statement. My apologies in advance for being such a bumbling beginner, & thanks again for anyone's help.

 

[c0i0nes]

I hate DateDiff(), who wants an age in years, months and days anyway? - Usually whole years are enough. After all, by the time you are ten you stop adding &quot;and a half&quot; to your age don't you?

Since Access uses days as the units in date/time calculations, you can use age = Int( x / 365.25 ) to equal a value in years, where x is an Access date/time value, and correcting for leap years, or if you want to correct for leap centuries as well, Int( x / 365.2425 ).

So (I've used the system date function Now() in these examples - put your own fieldnames in place):

age to 2 decimals=Int((Now()-[DateOfBirth])/3.652425)/100
age to 1 decimal=Int((Now()-[DateOfBirth])/36.52425)/100
age to 0 decimals=Int((Now()-[DateOfBirth])/365.2425)/100

Because of the nasty Access date window from 1930-2029, you get negative ages, because you haven't been rigorous in making date entry fields accept four-figure year values, have you? The bit of code that you need in the QBE grid to calculate a value for Age is:

Age: Iif((Int((Now()-[DateOfBirth])/365.2425)/100)< 0, Int((Now()-([DateOfBirth]-36524.25))/365.2425)/100,Int((Now()-[DateOfBirth])/365.2425)/100)

which corresponds to Access SQL of

Iif((Int((Now()-[DateOfBirth])/365.2425)/100)< 0, Int((Now()-([DateOfBirth]-36524.25))/365.2425)/100,Int((Now()-[DateOfBirth])/365.2425)/100) AS Age

Try this as an alternative to DateDiff() - there are a lot fewer brackets to trip over!

 

[Dbyte]

c0h0nes,
I had to make a few changes to your SQL statement because all values were shown as decimal #'s (ie .70, .74, .32, etc.) instead of whole #'s. Now it seems to be working fine. Here's what I ended up using:

IIf((Int((Now()-[Date of Birth])/365.2425)/100)<0,Int((Now()-([Date of Birth]-36524.25))/365.2425),Int((Now()-[Date of Birth])/365.2425)) AS Age

Removing &quot;/100&quot; from the last 2 Int portions gave me the format I needed for the #'s. Do you see any potential problems with this code? It seems fine to me. But I'm a beginner, so I wanted to confer with an expert before moving on to bigger & better things.

Thanks again for your help.