Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    127

    Unanswered: Working out the Age group

    I need to create a field in my query in Access, to work out the Age Bracket.



    This is worked out from a date field called [DateofSession] (02/01/2008) and [DOB] (19640729)



    Firstly I need to work out the age, minusing [DOB] from [DateofSession], and secondly to group them in an age bracket.



    I've started writing a query but it doesn't want to work,



    Please can someone help me



    Code:
     
    
    Age Bracket: IIf(([DateofSession]-misnumdate2date(Trim([DOB])))/365<='16','0 - 16',IIf(([DateofSession]-misnumdate2date(Trim([DOB])))/365 Between '16' And '30','16 - 30',IIf(([DateofSession]-misnumdate2date(Trim([DOB])))/365 Between '30' And '65','30 - 65',IIf(([DateofSession]-misnumdate2date(Trim([DOB])))/365 Between '65' And '75','65 - 75','75+'))))

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    DOB = Date of birth.

    What on earth value is this: 19640729 ??
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Posts
    127
    Quote Originally Posted by georgev
    DOB = Date of birth.

    What on earth value is this: 19640729 ??
    DOB does mean date of birth,

    the value 19640729 is the format that we use to store the dates, Year,Month,Day

    both of these fields have data_type of text and not date/time

    can someone help

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Convert them to a datetime datatype and then this is a non-issue

    When you've done that come back and I can show you how to get the results you are looking for.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi
    Quote Originally Posted by akhlaq768
    DOB does mean date of birth,

    the value 19640729 is the format that we use to store the dates, Year,Month,Day

    both of these fields have data_type of text and not date/time

    can someone help
    That's a disaster waiting to happen. In fact it probably already has. The very most basic constraint in a relational database is the datatype - why not use it?
    http://weblogs.sqlteam.com/jeffs/arc...and-Times.aspx

    Anyway - the starting point to calculating age is that you first need to convert your text strings (they are text strings - they are not dates and they are not numbers) into dates. A likely intermediate step is may have to tidy up strings that cannot be converted into dates. You then need to perform some calculations to get the age. Only once you have successfully done all this should you consider the bands.

    Age calcuations are not as simple as you might expect:
    http://www.everythingaccess.com/tuto...-given-the-DOB

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Age calcuations are not as simple as you might expect:
    http://www.everythingaccess.com/tuto...-given-the-DOB
    and yet, that article makes it look so easy...

    Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)

    notice how the expression (dtBDay > dtAsOf), which evaluates TRUE or FALSE, is used in an arithmetic calculation!!

    shazam!!! your age is the difference in years plus TRUE or FALSE!!

    i've seen mysql developers pull the same fast one

    the part that really annoys is that buddy wrote an actual function to calculate the age, in which he happily uses IFs to check a number of things (including, and this deserves a small kudos, the DOB not having happened yet!), but relies on a hack for the main calculation

    sigh and double sigh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2007
    Posts
    127
    is there a way i can change a text field to a date/time field, i want to perform this step automatically and not manually, because the process is all automated... and cannot be stopped for someone to manually change the datatype, can this be done in a macro or something similar.???

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    store the text strings as date values
    you can do this by creating appropriate DATETIME column(s)
    and then usign an update query that uses the cdate function

    because CDATE is written for the US market you will probably need to restate your date in US format (MM/DD/YY) and encapsulate it with the hash symbol "#"

    eg
    update <mytable> set <mynewdatecolumn> = cdate("#",mid$([DOB],5,2) & "/" & right$([dob],2) & "/" & left$([DOB],4) & "#"

    you need to replace the values in <> with appropriate values if you table was called persons that would be: update persons.........

    that makes no allowances for duffdata or missing data all of which you may need to consider

    however before you do that I'd suggest you do an impact analysis of which forms, queries and reports use the current DOB
    work out how much work is required to make the change, no matter how good your impact analysis is there will always be some fragments of code somewhere which trip you up on implementation.

    you need to make certain that whatever is the source of this data is also changed.... IM guessing its probably an ASCII import from a remote system.. I can't think of any other reason why in this day and age anyone would be using text for a date, and to remove the date separators smacks of trying to save an extra two characters per export. theres good reasons why things were done in a particular way years ago, but as hardware as dropped in price, computers are more capable and relational db's everywhere theres no need to hang on the the 'bad old ways' (unless you want to natch)

Posting Permissions

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