Trouble with age in ranges

[Zulema]

I am having problems with my query producing the correct information. I need the ages mapped into the correct ranges. I am getting duplicating information and not all ages are mapped to any range at all! Can someone please help?

My age field calculation looks like this:
Age: (#10/01/2007# - [DOB]/365.25

My range field calculation look like this:
AgeRange: Switch ([Age] >= 18 And [Age] < 64, "18 to 64", [Age] >= 65 And [Age] < 69, "65 to 69", [Age] >= 70 And [Age] < 100, "70 to 100")

[Scott Price]

There are a couple of things I notice with your code. First of all you need to change the Date into an Integer before doing your year division...

CInt(Date() - DOB)/365.25

. Date() is an Access function which gives today's date based on your computer's clock settings.

Second, you are leaving out a couple of age ranges by only specifying >= 18 And <64, then >=65 And <69, etc... This will result in 64 and 69 not being counted, as your are telling the db to only count values less than 64 and greater than or equal to 65, etc... Should be <=.

Regards,
Scott

[Zulema]

There are a couple of things I notice with your code. First of all you need to change the Date into an Integer before doing your year division...

CInt(Date() - DOB)/365.25

. Date() is an Access function which gives today's date based on your computer's clock settings.

Second, you are leaving out a couple of age ranges by only specifying >= 18 And <64, then >=65 And <69, etc... This will result in 64 and 69 not being counted, as your are telling the db to only count values less than 64 and greater than or equal to 65, etc... Should be <=.

Regards,
Scott

Thanks for you help. I corrected the code for the age field and changed the range field but still having trouble.

I changed the range field to this:
AgeRange:Switch ([Age]>= 18 And [Age]<=64, "18 to 64", [Age]>=65 and [Age]<=69,"65 to 69",[Age]>=70 And [Age]<= 100, "70 to 100"

[Scott Price]

Are you doing all this in one query?

I recreated as closely as I could in my test database using two queries, the first to calculate the Age, the second including the first exactly as you have it, to calculate the AgeRange. It correctly identified each test value I entered.

The DOB field I set as Date/Time data type.

An interesting error occurred while I was playing with this: a DOB before 1918 produced a result of #Error. Still not sure why yet, but I'm doing a little more testing on that :-)

Regards,
Scott

[Scott Price]

OK, figured it out. Integer data type has values between -32,768 and 32,767. Using a DOB of earlier than about July of 1918 results in a calculation that exceeds that threshold, this causes an overflow error meaning that you have exceeded the limits of the type declaration.

Using CLng() solves the problem. This changes to a Long Integer value, which has a limit of a little over 2 billion. I doubt you'll run into someone quite that old :-)

Another thought is that you can include the calculation in the AgeRange: Switch() function: For example

AgeRange = Switch(CLng(Date - DOB) / 365.25 >= 120, "Older than dirt")

Regards,
Scott

[Zulema]

I got errors too, but it was because i had blank DOB fields.
My query is still not producing the correct birthdates. I went back and queried to calculate the age without having a range field but it doesn't calculate correctly for people age 64.
For example: A person with DOB of 09/27/1943 is showing age 64 and below that row, on the same query, is another person DOB 08/26/1943 showing age 65!
When i run the 2nd query including the range field it returns blanks for these people! I really appreciate you helping me with this!!

[Scott Price]

First a couple of questions:

What data type are you storing your dates as?

How many decimal points are you restricting your display to?

Running this in my test db I come up with Age
64.457221081451 0609171800 for the first DOB you gave in 1943 and Age
64.544832306639 2881587953 for the second. This makes me suspect that you have a data type setting of Integer or something like it that prevents the decimal from being displayed as a decimal and is rounding the displayed results up or down. In the second query, it is calculating correctly, as you haven't provided for the 0.99999999999 range of values between 64 and 65. Change to 64.99999999 or better yet, <65 and you'll include those problem values. You'll need to do this for each age range, of course, as well as adding an age range for under 18 and over 100.

Regards,
Scott

[Zulema]

First a couple of questions:

What data type are you storing your dates as?

How many decimal points are you restricting your display to?

Running this in my test db I come up with Age
64.457221081451 0609171800 for the first DOB you gave in 1943 and Age
64.544832306639 2881587953 for the second. This makes me suspect that you have a data type setting of Integer or something like it that prevents the decimal from being displayed as a decimal and is rounding the displayed results up or down. In the second query, it is calculating correctly, as you haven't provided for the 0.99999999999 range of values between 64 and 65. Change to 64.99999999 or better yet, <65 and you'll include those problem values. You'll need to do this for each age range, of course, as well as adding an age range for under 18 and over 100.

Regards,
Scott

I get 64.457221814510 60917180013689 for the first DOB and 64.544832306639 288158795345654 for the second.
Where should i correct the data setting to be able to display the correct rounding of the age?

[Scott Price]

Where are you getting a display of 65 for the second dob if, as you are telling me, you get the 64.5blablabla?

Regards,
Scott

[Zulema]

Where are you getting a display of 65 for the second dob if, as you are telling me, you get the 64.5blablabla?

Regards,
Scott

I get the 64.5..... on the 2nd query.

[Scott Price]

What's the SQL of your second query? Please post it in it's entirety, Thanks!

Regards,
Scott

[Zulema]

What's the SQL of your second query? Please post it in it's entirety, Thanks!

Regards,
Scott

This is what I have on the 2nd qry:

[CODE=sql]SELECT EMPLOYEES.SECTI ON, EMPLOYEES.DEPT, EMPLOYEES.DOB,E MPLOYEES.LAST, EMPLOYEES.FIRST , EMPLOYEES.MIDDL E, EMPLOYEES.[Adj LIFE SALARY],[Adj LIFESALARY]*0.00003 AS ADDRATE,[Adj LIFE SALARY]*0.000162 AS LIFERATE, CInt(Date()-[DOB]/365.25 AS Age, Switch(CLng(Dat e()-[DOB])/365.25>=120, "older than dirt", [Age]>=18 And[Age]<65,"18 to 64",[Age]>=65 And [Age]<70,"65 to 69",[Age]>=70 And [Age]<100, "70 to 100") AS AgeRange
FROM EMPLOYEES
[/CODE]
This gave me the correct ages, so it worked! but..... Now, what if i need to calculate the age these people were in October 2007. I am trying to produce a Insurance Life Report and the fees and rates are based on the age that the person is every year in October.

[Zulema]

This is what I have on the 2nd qry:

SELECT EMPLOYEES.SECTI ON, EMPLOYEES.DEPT, EMPLOYEES.DOB,E MPLOYEES.LAST, EMPLOYEES.FIRST , EMPLOYEES.MIDDL E, EMPLOYEES.[Adj LIFE SALARY],[Adj LIFESALARY]*0.00003 AS ADDRATE,[Adj LIFE SALARY]*0.000162 AS LIFERATE, CInt(Date()-[DOB]/365.25 AS Age, Switch(CLng(Dat e()-[DOB])/365.25>=120, "older than dirt", [Age]>=18 And[Age]<65,"18 to 64",[Age]>=65 And [Age]<70,"65 to 69",[Age]>=70 And [Age]<100, "70 to 100") AS AgeRange
FROM EMPLOYEES

This gave me the correct ages, so it worked! but..... Now, what if i need to calculate the age these people were in October 2007. I am trying to produce a Insurance Life Report and the fees and rates are based on the age that the person is every year in October.

The rates are also based on the age that is why i was thought to map the ages out in ranges. Am headed the right direction? I am very new to this and your help has been so great! Thanks!

[Scott Price]

There are two ways to do this, probably the best is to make your query Parameterized.

Here is an example of how to do this:

[CODE=sql]PARAMETERS NewYear DateTime;
SELECT CLng([NewYear]-[DOB])/365.25 AS Age, Table3.id, Table3.dob
FROM Table3;[/CODE]

By the way, when posting code, SQL or VBA or otherwise, please use the CODE tags. It's easy to do, just select your code text in the reply window, then click on the # icon on the top of the reply window. As an added bonus you can manually edit the first code tag to reflect what code language you are posting. [CODE=sql] is an example of sql, =vb is for vba, etc.

Another thing I noticed is that you are still using the CInt in your Age calculation. As I mentioned earlier, this will result in an overflow error when used with DOB's of earlier than 1918 or so. You changed to CLng in the second, just remember to change the other one too :-)

Regards,
Scott

[jaxjagfan]

The rates are also based on the age that is why i was thought to map the ages out in ranges. Am headed the right direction? I am very new to this and your help has been so great! Thanks!

I prefer using DateDiff to specify numerical differences in dates:

SELECT 2007 AS YR,
EMPLOYEES.SECTI ON, EMPLOYEES.DEPT, EMPLOYEES.DOB,E MPLOYEES.LAST, EMPLOYEES.FIRST , EMPLOYEES.MIDDL E, EMPLOYEES.[Adj LIFE SALARY],[Adj LIFESALARY]*0.00003 AS ADDRATE,[Adj LIFE SALARY]*0.000162 AS LIFERATE,
DateDiff("y",[DOB], CDate("10/31/" & [YR])) AS Age,
iif([Age]>100,"Older Than Dirt",iif([Age] Between 70 and 100,"70 to 100",iif([Age] Between 65 and 69,"65 to 69",iif([Age] Between 18 and 64,"18 to 64", "Less than 18"))))
AS AgeRange
FROM EMPLOYEES

Your rate table needs to have Min and Max Age Columns since the rate varies depending on age. Or an age group column (1 thru however many groups - actually 5 in your case - the 4 you listed as well as less than 18).

SELECT 2007 AS YR,
EMPLOYEES.SECTI ON, EMPLOYEES.DEPT, EMPLOYEES.DOB,E MPLOYEES.LAST, EMPLOYEES.FIRST , EMPLOYEES.MIDDL E, EMPLOYEES.[Adj LIFE SALARY],[Adj LIFESALARY]*0.00003 AS ADDRATE,[Adj LIFE SALARY]*0.000162 AS LIFERATE,
DateDiff("y",[DOB], CDate("10/31/" & [YR])) AS Age,
iif([Age]>100,5,iif([Age] Between 70 and 100,4,iif([Age] Between 65 and 69,3,iif([Age] Between 18 and 64,2, 1))))
AS AgeGroup
FROM EMPLOYEES

Once you got the AgeGroup value finding the rate that matches the group could be accomplished using an HLookup in a rate table.