By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Trouble with age in ranges

P: 44
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")
Mar 12 '08 #1
Share this Question
Share on Google+
16 Replies


Scott Price
Expert 100+
P: 1,384
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...

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 12 '08 #2

P: 44
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...

Expand|Select|Wrap|Line Numbers
  1. 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"
Mar 12 '08 #3

Scott Price
Expert 100+
P: 1,384
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
Mar 12 '08 #4

Scott Price
Expert 100+
P: 1,384
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
Expand|Select|Wrap|Line Numbers
  1. AgeRange = Switch(CLng(Date - DOB) / 365.25 >= 120, "Older than dirt")
Regards,
Scott
Mar 12 '08 #5

P: 44
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!!
Mar 12 '08 #6

Scott Price
Expert 100+
P: 1,384
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.4572210814510609171800 for the first DOB you gave in 1943 and Age
64.5448323066392881587953 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
Mar 12 '08 #7

P: 44
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.4572210814510609171800 for the first DOB you gave in 1943 and Age
64.5448323066392881587953 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.45722181451060917180013689 for the first DOB and 64.544832306639288158795345654 for the second.
Where should i correct the data setting to be able to display the correct rounding of the age?
Mar 12 '08 #8

Scott Price
Expert 100+
P: 1,384
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
Mar 12 '08 #9

P: 44
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.
Mar 13 '08 #10

Scott Price
Expert 100+
P: 1,384
What's the SQL of your second query? Please post it in it's entirety, Thanks!

Regards,
Scott
Mar 13 '08 #11

P: 44
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT EMPLOYEES.SECTION, EMPLOYEES.DEPT, EMPLOYEES.DOB,EMPLOYEES.LAST, EMPLOYEES.FIRST, EMPLOYEES.MIDDLE, 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(Date()-[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
  2. FROM EMPLOYEES
  3.  
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.
Mar 13 '08 #12

P: 44
This is what I have on the 2nd qry:

SELECT EMPLOYEES.SECTION, EMPLOYEES.DEPT, EMPLOYEES.DOB,EMPLOYEES.LAST, EMPLOYEES.FIRST, EMPLOYEES.MIDDLE, 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(Date()-[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!
Mar 13 '08 #13

Scott Price
Expert 100+
P: 1,384
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:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS NewYear DateTime;
  2. SELECT CLng([NewYear]-[DOB])/365.25 AS Age, Table3.id, Table3.dob
  3. FROM Table3;
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
Mar 13 '08 #14

jaxjagfan
Expert 100+
P: 254
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.SECTION, EMPLOYEES.DEPT, EMPLOYEES.DOB,EMPLOYEES.LAST, EMPLOYEES.FIRST, EMPLOYEES.MIDDLE, 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.SECTION, EMPLOYEES.DEPT, EMPLOYEES.DOB,EMPLOYEES.LAST, EMPLOYEES.FIRST, EMPLOYEES.MIDDLE, 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.
Mar 13 '08 #15

P: 44
I got it to work. Thank you all for your very valuable and appreciated help!
Mar 20 '08 #16

Scott Price
Expert 100+
P: 1,384
Not a problem! Glad you got something working, and thanks for letting us know.

Regards,
Scott
Mar 20 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.