Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64

    Unanswered: Calculating difference between two ages

    I need some advice with the following problem. I am setting up a database in Access which stores children's reading ages and calculates the difference between their reading age and chronological age. The inputs are the Reading Test Date, and the Reading Age. I have created a query which uses the DateDiff function to calculate the child's chronological age on the test date, but cannot work out how to calculate the difference between the reading age and the chronological age. The ages look like decimals, but of course they're not actually decimals, since 5.11 (for example) means 5 years and 11 months. So the difference between 5.11 and 6.2 would be 0.3 (no years and three months).
    Any help / suggestions would be gratefully received!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would calculate your ages as decimals in the first instance, subtract them and then convert to years\ months. The 5.11 notation is a presentation issue and not really for use in calculations.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Thanks for the suggestion. Actually, that was the first thing I tried but I was getting silly results with some (but not all) calculations. It may have been that my algorithm for doing the conversion into Years and Months was ropey so I'll give it another go. I agree the "decimal" notation I have used is presentation - I could have just as easily used 5 y 11 m or something similar.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is your algorithm? Perhaps we can tidy it....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Years = Int(Age)
    Months = 12 / (Age - Int(Age))

    My tired eyes think that looks reasonable.
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    That's pretty much the way I was converting ages in years/months to decimals. I did this for both ages (i.e. reading age and chronological age) then subtracted chron. age from reading age. So far so good. But reversing the calculation (i.e. multiplying by 12) to convert back to years/months gives, in some cases, stupid results. I'm clearly doing something wrong, probably something really obvious!

  7. #7
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello kabuki!

    I think I have solved your problem.
    Look at "DemoCalcDifAgeA2000.mdb"
    Open Form1 and try. Look at VBA (on current event).
    Adapt it as you need.
    Attached Files Attached Files

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There is a mistake in what I put above - should be divide by 12, not 12 divided by...

    I've had a better idea - store all ages etc in months!
    This will make calculations miles easier.

    Example: Converting decimals into months
    CronAge = 8.833333.... (8 years 10 months)
    CronAgeInMonths = 8.833333... * 12
    = 106 months

    ReadAge = 9.75 (9 years 9 months)
    ReadAgeInMonths = 9.75 * 12
    = 117

    Example: Working out the difference
    AgeDiff = ReadAge - CronAge
    = 117 - 106
    = 11 months

    Example: Working out someones age from the months stored
    CronAge = 106 months
    Years = Int(106 / 12)
    = 8
    Months = 106 mod 12
    = 10
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    I forgot, my Decimal simbol is "," (not ".").

  10. #10
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Many thanks to georgev and mstef-zg: together you have solved my problem! I would buy you both a beer if you lived close enough!

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I accept paypal payments hehe
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Many thanks on the beer,
    I live close enough, but I
    don't know where do you live.

  13. #13
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Luton, Bedfordshire - many miles from Croatia!

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Bedfordshire eh... I'll bear that in mind if/when I have to travel down
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •