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
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+'))))
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 = 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
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.???
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)