Full Version: Calculating Age
UtterAccess Forums > Microsoft® Access > Access Date + Time
stufer
Hello
I'm having a little trouble with the DateDiff function in a query I set up a little while ago.
Oset this up to calculate the age of people so that I didn't have to keep on referring to the date of birth and work everything out from that.
The formula I used is this: Age: DateDiff("yyyy",[DOB],Date())
What I wanted this to do is update the number of years old a person was when their birthday comes around.
This at first appeared to operate correctly. At least I thought it did only update when a birthday came up.
However, after the new year rolled around and I returned to work, I noticed that all ages have advanced by one year even though the birthday hasn't come around yet.
I can't figure out how (or even if its possible) to make it update only when that birthday comes around. I did try this:
Age: DateDiff("d",[DOB],Date())/365.25
but that gave me ages with lots of decimal places after them, and despite specifying that I wanted it to display NO decimal places, it still did. Besides, this isn't a very elegant solution because each person's age would update on exactly the right day once every 4 years rather than all the time.
Could some kind soul let me know of a solution to this or let me know if it is not possible. If it isn't possible then I guess I will do it by the month and not be so fussy about the exact day.
Thanks in advance,
Stu.
freakazeud
Hi,
welcome to UA forums.
Have a look here calculate age to correctly calculate the age. Furthermore you should NOT store this in a table. It can be calculated at runtime on report or forms or in queries.
HTH
Good luck
stufer
Thank you sir, you are a gentleman.
That's sorted me out a treat.
Yes, I take your point about the table, that's how I started trying to do it but when I realised I couldn't, I made a query, then fed that into the form.
Cheers again, mate
Stu.
freakazeud
You're welcome.
Glad I could assist.
Good luck on future projects!
stufer
Actually mate, you may be able to help with this. I did the stuff that the post you pointed me to said and that works great - the age shows up correctly on the form.
However, I've just been trying to use it in a query (ie. trying to query for everyone who is 12 on the database) and whenever I type a number into the criteria for the 'Age' field, it comes up with the error "Data type mismatch in criteria expression". With the first formula I mentioned, it would find all 12 year olds (albeit incorrectly as I now know) but I can't seem to query this one. Any ideas why?
freakazeud
Hi,
it should work. Create a new column in your query and put that as the name:
ge: DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))
Now in the criteria of this column you could filter for specific ages e.g. 12
What datatype is your DOB field in the table?
HTH
Good luck
stufer
Hi,
The DOB field data type is Date/Time, format is Short Date and the input mask is 00/00/00.
ow, not everyone on the database has a DOB entered and so in my query, some of the records (those with no DOB) show up #Error in the Age field.
I thought this might be the problem as it may not see the datatype for the Age field as being a number.
So I tried to remove these errorneous fields by specifying the criteria for the DOB field as Is Not Null. But it still says there is a datatype mismatch.
HAs well as specifying criteria, I tried a basic filter and that came up with the same error.
It seems odd to me because there isn't anything massively different about these formulae, one is just a bit more extensive.
Thanks for your quick replies and very helpful attitude by the way. It is most impressive.
Stu.
freakazeud
Hi,
can you attach the db so I can take a look at it. It is hard to understand what's going on without having it in front of me. Keep it under 500kb, zipped and leave confidential data out.
HTH
Good luck
stufer
Hi there,
I was just compacting the database down to attach here. I got rid of all the data etc (in a copy of course) and stripped it down to the bare essentials.
Oremoved all the data in there and added some names of my own. All of them had a DOB entered.
I tried to enter criteria to search on and it worked perfectly.
Then I added one record without a date of birth and it began giving the error message again, so that is clearly the problem.
Anyway, I've attached the file as requested if you do get a chance to look at it.
Thanks again.
Stu.
Larry Larsen
Hi Stu
You can use IIF() around the age calculation to evaluate if DOB is null then replace their age with "No DOB".. message.
eg:
CODE
Age: IIf(IsNull([DOB]),"[color="red"]No DOB[/color]",DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB]))))

PS Sorry Oli.. for jumping in.
HTH's
thumbup.gif
freakazeud
Hi,
you need to seriously normalize your data. Your table structure is wrong.
You cannot just have ONE table with ALL those fields in them. You need to read up on normalization and how to create correct data models. Access is a database development tool and not a flat data spreadsheet tool like excel. They behave both completly different. Data structure and normalization are the most importent thing in database development. Everything is based on it and if it is incorrect then you will run in big problems later on. You should not worry about creating queries or forms or code until you have your structure correct.
After reading up on normalization here in the provided links you can repost your table structure the way you think it should work and we can ensure you if it is the best set up. Only then you can go on and worry about other things.
HTH
Good luck
freakazeud
No problem...I wanted to be sure the OPs set up is correct and appearently not at all, which I was predicting.
stufer
That's it!!
ice one mate, I have full functionality back. Very clever stuff.
I'll certainly use these forums again. Very helpful and kind bunch of people you all are.
Particular thanks to freakazeud and Larry Larsen.
Was getting so narked about this that I was just going to go home, stay in and drink tonight. But now I think I'll go out and drink instead.
You've saved me a lot of work, guys.
Thanks
Stu.
Larry Larsen
Hi
No problem and please take on board the advice given by Oliver(freakazeud).
thumbup.gif
stufer
freakazeud,
hat's not my only table. I just stripped it down to that to save space when I zipped it to you. I will have a look at those notes on normalisation, cheers for the link.
Do you think that it is a problem then to have all those fields in one table? I set it like that because they were all things that related to the personal details and it seemed to make sense to me to have them together.
There are other tables which relate to the services we offer, which take the info from that table and transfer the name and Person_ID to the new table.
Still, getting towards 5 on Friday pm now so I won't have a look at those things until next monday. Looks like I've got a lot of reading to do.
Cheers,
Stu.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.