exact age calculated on DOB

[Chintsapete]

Hi all

I don't seem to find any query formula in the forum where the age changes on the date of birth. I tried all the once I could find but all of them seem to calculate the age as of 1 January. I find it a bit strange that it doesn't work. Anyone has a suggestion.
I got BirthDate and Date in the table I want to update the age column back in the history with an update query.

Int(DateDiff("yyyy",[BirthDate],[Date]))

Int(DateDiff("d", [BirthDate], DateSerial(DatePart("yyyy,[Date]), 1, 1))/365.25)

Int(DateDiff("yyyy",[Birthdate],[Date]))+Int(Format([Date],"mmdd")<Format([Birthdate],"mmdd"))

All the above change the age on January 1. It's not a train smash but weird.
Cheers
Pete

 

[CJ_London]

datediff returns a long value, so the use of int is irelevant and may cause an error if datediff returns a number out of range.

What 'age' are you trying to calculate? years or years/months or years/months/days or decimal years or something else?

You may have your reasons but why are you doing this to update a field in a table and not just calculating on the fly? it will potentially be wrong tomorrow....

 

[Chintsapete]

HI CJ

I do calculate on the fly now, but never done previously and I'm converting my historic data to the new format of the new database I've written. And the age is one of the columns missing previously. Hence the update query.
What I'm trying to do is calculate the years, but so the age changes over on the Date of Birth and not on January 1. The same applies for years of engagement.
The date I'm using is the weekly pay run date and I'd just like to have it accurate for every entry to that date.
As I said earlier, it's not a super train smash but why the inaccuracy? Surely there must be a way to calculate it.
Cheers
Pete

 

[CJ_London]

try

DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate])))

 

[Chintsapete]

Hi CJ

I tried that one, but it does the same thing. I just altered the code to the pay run date as below.

DateDiff("yyyy",[Birthdate],[Date])+([Date]<DateSerial(Year([Date]),Month([Birthdate]),Day([Birthdate])))

Cheers
Pete

 

[Cotty42]

Try this

[FONT=Calibri]Age = DateDiff("yyyy", [DOB], Date) - IIf(Month(Date) < Month([DOB]), 1, IIf(Month(Date) = Month([DOB]) And Day(Date) < Day([DOB]), 1, 0))[/FONT]

 

[plog]

I got BirthDate and Date in the table I want to update the age column back in the history with an update query.

You shouldn't store data that can be calculated. Age shouldn't be a field in a table if you have date of birth. Instead, you simply run a query and claculate it when you need it.

I think age is a complex enough calculation that you shouldn't use an expression to calculate it. Instead I think a function is the way to go:

Function get_Age(dob, Optional d2)
    ' determines age on date (date2) based on birthdate(dob), uses Current date for default of date2

ret = -1

If IsMissing(d2) Then d2 = Date
    ' if no second date, uses today
    
ret = DateDiff("yyyy", dob, d2) - 1
If (Month(dob) < Month(d2)) Then ret = ret + 1
If (Month(dob) = Month(d2)) And (Day(dob) <= Day(d2)) Then ret = ret + 1
    ' compares two dates to see how old

getAge = ret

End Function

That function will calculate the age on any date (d2) based on date of birth (d1). This will allow you to use it to determine someone's age on any given date. You would use it in a query like so:

Age: get_Age([DateOfBirthField], Date())

 

[CJ_London]

I tried that one, but it does the same thing

you haven't tried it quite the same

[Date] should be Date() - no square brackets. If [Date] is the name of a field then you need to change the name - it is a reserved word

 

[namliam]

Int(DateDiff("yyyy",[Birthdate],[Date]))+Int(Format([Date],"mmdd")<Format([Birthdate],"mmdd"))

Indeed if the [date] is replaced to date() it should work just fine, gives me my proper age of 89

 

[Chintsapete]

Hi CJ
Ok, yes the field is called date, is that the gremlin? The reserved words?
I will have to look at that carefully first before I can change the name with all the queries depending on it but will definitely try it out. Will let you know. Thanks a lot for your help.

And for grandpa (89) Namliam , I think with your formula and the reserved word "date" it comes back to what I tried. The problem is that I need to fill in the history table and I can't use Date(), I need to use the pay run date. But I will try CJ's suggestion as mentioned above. Thanks.

Thanks Plog, I'm just not good enough for the functions, never understood how to program them. I did try to convert your function into a query though which worked to the same end as the other one, that's why I think CJ hit the nail on the head with the reserved word.

As always thank you all for your time and help, I'll post when it works.
Shot a lot
Pete

 

[namliam]

Int(DateDiff("yyyy",[Birthdate],[YourPayDate]))+Int(Format([YourPayDate],"mmdd")<Format([Birthdate],"mmdd"))

Assuming Birthdate and YourPayDate are both proper date fields, that should work just fine.

 

[Chintsapete]

This is ridiculous.

I hate to disappoint, but non of your suggestions work. They all change the date on the Jan 1. Incl. Cotty42, sorry didn't see your post earlier and thanks.

I changed the date field in the table to PrDate and checked the properties. They are date/time and indexed and format is short date in both tables.
Is there a possibility that the reserved word "date" is stuck behind the scenes somewhere?
One should think this should be a simple matter of DateDiff, but it isn't. What I really can't understand is that it was dead accurate with the cycle formula you guys helped me with and now it doesn't work.
As you said Namliam, the next problem is just around the corner,.
Cheers
Pete

 

[Chintsapete]

I just noticed that the problem seems to be the update query. I just thought of it now. I made a select query and used one of the formulas and there it works. Haven't checked all of them but they possibly all work. Might have to do it in a roundabout way.
I don't know why it will not calculate in the update query field "update to" though.

 

[CJ_London]

why don't you post your complete update query.

 

[Chintsapete]

I stripped down the table else it's way to big for upload, but I hope it works and there is enough data in it for the argument.
Just be aware that the few extra fields I just put in to check the data before I run the query, although I realized now that I must have accidentally run it at some stage anyway which shouldn't matter.
But for sure the formulas work on a select query. So not really sure why not on the update query.
Cheers

 

[CJ_London]

You do realise that your update query is

a) trying to update two tables - you can't, see d below
b) is updating the salaries ytd emp# field to 1 - so all emp#=3 are becoming emp#=1
c) is updating the salaries ytd prDate field to 1 - which is not valid
d) suspect the update of employee detail is not required - can't see any reason why you would want to change dateofengagement and emp# to 1

And although you have changed the field name from Date to prDate in the table you have not modified the equation to account for this.

You are also still using reserved words - in this case Index - although this will not affect this particular outcome.

Correct for all the above and it will work for either my suggestion or the mailman's

 

[CJ_London]

sorry - just realised you said about the few extra fields so some of my comments probably relate to that.

correct the formula and it works.

I'm wondering if you are selecting the view from the query design rather than actually running the query. View just runs a select query and displays the current data and not the updated data.

 

[Chintsapete]

I'm wondering if you are selecting the view from the query design rather than actually running the query. View just runs a select query and displays the current data and not the updated data.

Of course, what an $^@&*@* I am sometimes, you so right, that's exactly what I did. Eish, sorry to waste all your time, with something like that. I had a complete block on that one.
Thanks for all your time and apologies again.
Pete

 

[Chintsapete]

Hi all

just to summarize the above, the code below definitely works.

Int(DateDiff("yyyy",[Birthdate],[Date]))+Int(Format([Date],"mmdd")<Format([Birthdate],"mmdd"))

There are probably a few others of them that work as well but I didn't have the time to check them all out.
I'd like to thank all the guys who contributed in this post to help overcome my stupidity with testing the code and apologize again for wasting their time.
Pete

 

[Chintsapete]

Sorry I forgot to mention, the reserved word "Date" didn't matter for that query. It worked anyway.