Expr. to calculate 'Age' from Date field

[JohnB47]

Hi.

If I have a Date/Time field in a table/form, what expression could I introduce so that another field shows the expired time from that date up to todays date, in Years, Months and Days?

e.g. if I have a field 'TRDate' which shows 1st Aug 2011 and todays date is 9th Aug 2011, a second field 'Age' would show 8 days, and if TRDate shows 1st Jan 2010, 'Age' would show 1 year, 8 months, 8 days (if my calculation is correct).

Also, how would I use the expression so that, when a date is entered in TRDate, the table/field 'Age' gets automatically updated?

Thanks.

[blazerboy6]

Try

=IIf(Date()<=[tsDate],0,IIf(Date()-[tsDate]=1,Date()-[tsDate] & " day",Date()-[tsDate] & " days"))

This should return a 0 for age if TRDate is in the future, and give you a number of days from today if TRDate is in the past (ie "1 day", "2 days", "3 days", "365 days", etc). You might want to try to play around with the Format$ function, but i'm not sure how to give you "1 day, 8 months, 8 days" as a formatted option.

As for your second question, if this is going to just be a text box within the form and not an append query or something, I think it will auto-update the age value if you change or add a date in the form. If it doesn't work as is, you could do either a macro or stored procedure in the "after update" Event in the properties tab of the 'TRDate' field to refresh or requery.

[blazerboy6]

ok, it was bugging me, so here you go:

=IIf(Date()<=[TRDate],0,DateDiff("yyyy",[TRDate],Date()) & " Year(s), " & DatePart("m",Date())-DatePart("m",[TRDate]) & " Month(s), " & DatePart("d",Date())-DatePart("d",[TRDate]) & " day(s)")

gave me "2 Year(s), 2 Month(s), 2 day(s)" with TRDate = 6/7/2009 with a total number of days = 793

[JohnB47]

Try

=IIf(Date()<=[tsDate],0,IIf(Date()-[tsDate]=1,Date()-[tsDate] & " day",Date()-[tsDate] & " days"))

This should return a 0 for age if TRDate is in the future, and give you a number of days from today if TRDate is in the past (ie "1 day", "2 days", "3 days", "365 days", etc). You might want to try to play around with the Format$ function, but i'm not sure how to give you "1 day, 8 months, 8 days" as a formatted option.

As for your second question, if this is going to just be a text box within the form and not an append query or something, I think it will auto-update the age value if you change or add a date in the form. If it doesn't work as is, you could do either a macro or stored procedure in the "after update" Event in the properties tab of the 'TRDate' field to refresh or requery.

Thanks - I'll have a play with that. I'll get back with progress or further questions.

Cheers.

[JohnB47]

ok, it was bugging me, so here you go:

=IIf(Date()<=[TRDate],0,DateDiff("yyyy",[TRDate],Date()) & " Year(s), " & DatePart("m",Date())-DatePart("m",[TRDate]) & " Month(s), " & DatePart("d",Date())-DatePart("d",[TRDate]) & " day(s)")

gave me "2 Year(s), 2 Month(s), 2 day(s)" with TRDate = 6/7/2009 with a total number of days = 793

Oops - you posted this while I was replying to your earlier one. Thanks - I appreciate your perserverance. It will be tomorrow before I can get back to you with progress.

Cheers.

[JohnB47]

Oh, I couldn't resist it - I've had a play.

Yes, it works - mostly. The only problem I have is that sometimes it shows a minus sign before some of figures.

E.g. I put the date 15/10/2009 in the date field and got 2 Year(s), -2 Month(s), -6 day(s) in the Age field.

Sorry to be a pain.

Thanks for the help.

[blazerboy6]

here you go... Abs(<<number>>) returns the absolute value, so this should work.

=IIf(Date()<=[tsDate],0,DateDiff("yyyy",[tsDate],Date()) & " Year(s), " & Abs(DatePart("m",[tsDate])-DatePart("m",Date())) & " Month(s), " & Abs(DatePart("d",[tsDate])-DatePart("d",Date())) & " day(s)")

oh, this won't work though, since DatePart() give the month value, there's no difference between 6-2 and 6-10...

let me try something else...

[JohnB47]

here you go... Abs(<<number>>) returns the absolute value, so this should work.

=IIf(Date()<=[tsDate],0,DateDiff("yyyy",[tsDate],Date()) & " Year(s), " & Abs(DatePart("m",[tsDate])-DatePart("m",Date())) & " Month(s), " & Abs(DatePart("d",[tsDate])-DatePart("d",Date())) & " day(s)")

oh, this won't work though, since DatePart() give the month value, there's no difference between 6-2 and 6-10...

let me try something else...

Thanks - I do appreciate the time you're taking on this. Actually, I thought this sort of code would have been very common - included somehow in Access as a feature even. Don't lots of people want to use Access to work out the ages of things?

Cheers.

[blazerboy6]

=(IIf(Date()-[TRDate]<365,0,IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),Int(DateDiff("m",[TRDate],Date())/12),Int((DateDiff("m",[TRDate],Date())-1)/12)))) & " Year(s), " & ((IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),DateDiff("m",[TRDate],Date()),DateDiff("m",[TRDate],Date())-1))-((IIf(Date()-[TRDate]<365,0,IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),Int(DateDiff("m",[TRDate],Date())/12),Int((DateDiff("m",[TRDate],Date())-1)/12))))*12)) & " Month(s), " & (IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),DatePart("d",Date())-DatePart("d",[TRDate]),(DatePart("d",Date())-DatePart("d",[TRDate]))+(Day(DateSerial(Year([TRDate]),Month([TRDate])+1,0))))) & " day(s)"

[blazerboy6]

=(IIf(Date()-[TRDate]<365,0,IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),Int(DateDiff("m",[TRDate],Date())/12),Int((DateDiff("m",[TRDate],Date())-1)/12)))) & " Year(s), " & ((IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),DateDiff("m",[TRDate],Date()),DateDiff("m",[TRDate],Date())-1))-((IIf(Date()-[TRDate]<365,0,IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),Int(DateDiff("m",[TRDate],Date())/12),Int((DateDiff("m",[TRDate],Date())-1)/12))))*12)) & " Month(s), " & (IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),DatePart("d",Date())-DatePart("d",[TRDate]),(DatePart("d",Date())-DatePart("d",[TRDate]))+(Day(DateSerial(Year([TRDate]),Month([TRDate])+1,0))))) & " day(s)"

yeah, it's big and scary, but I couldn't resist a challenge... Let me know if this doesn't work for you...

The issue with Access is not in getting it to calculate something for you, DateDiff() will do the days easily, it's getting the data/forms to be user friendly and to display the data we want how we want to see it...

[JohnB47]

Thanks - I appreciate your time on this.

I'm a bit busy today. I'll try it out later and will get back.

Cheers.

[blazerboy6]

=(IIf(Date()-[TRDate]<(DatePart("y",(DateSerial((DatePart("yyyy",[TRDate])),12,31)))),0,IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),Int(DateDiff("m",[TRDate],Date())/12),Int((DateDiff("m",[TRDate],Date())-1)/12)))) & " Year(s), " & ((IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),DateDiff("m",[TRDate],Date()),DateDiff("m",[TRDate],Date())-1))-((IIf(Date()-[TRDate]<(DatePart("y",(DateSerial((DatePart("yyyy",[TRDate])),12,31)))),0,IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),Int(DateDiff("m",[TRDate],Date())/12),Int((DateDiff("m",[TRDate],Date())-1)/12))))*12)) & " Month(s), " & (IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),DatePart("d",Date())-DatePart("d",[TRDate]),(DatePart("d",Date())-DatePart("d",[TRDate]))+(Day(DateSerial(Year([TRDate]),Month([TRDate])+1,0))))) & " day(s)"

Leap years... replaced the "365" with a function for counting the days of the year in [TRDate]'s year. So this above should hold up around leap year dates as well now...

[JohnB47]

=(IIf(Date()-[TRDate]<(DatePart("y",(DateSerial((DatePart("yyyy",[TRDate])),12,31)))),0,IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),Int(DateDiff("m",[TRDate],Date())/12),Int((DateDiff("m",[TRDate],Date())-1)/12)))) & " Year(s), " & ((IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),DateDiff("m",[TRDate],Date()),DateDiff("m",[TRDate],Date())-1))-((IIf(Date()-[TRDate]<(DatePart("y",(DateSerial((DatePart("yyyy",[TRDate])),12,31)))),0,IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),Int(DateDiff("m",[TRDate],Date())/12),Int((DateDiff("m",[TRDate],Date())-1)/12))))*12)) & " Month(s), " & (IIf(DatePart("d",[TRDate])<=DatePart("d",Date()),DatePart("d",Date())-DatePart("d",[TRDate]),(DatePart("d",Date())-DatePart("d",[TRDate]))+(Day(DateSerial(Year([TRDate]),Month([TRDate])+1,0))))) & " day(s)"

Leap years... replaced the "365" with a function for counting the days of the year in [TRDate]'s year. So this above should hold up around leap year dates as well now...

I am very sorry to have left it so long to get back to you. Pressure of work - my apologies.

OK, I've just tried it but it gives me #Name? in the txt field I'm putting it in. Sadly I don't have the knowledge to discover the problem. Please don't work too hard on this - I would like to use it but I feel bad that I left it so long.

Thanks for your help.

[blazerboy6]

JohnB47,

I'm not sure what to tell you. That error message will come up if the field inside the brakets (ie. [TRDate]) is not the same as the field name in your database. If you put in the code and one of the instances of [TRDate] was misspelled, or the name was different in your database, then the view of the control from Form View would display #NAME?. When I put it in my database to test it, my field name was [tsDate], so I copied it into a blank MSWord document and did a find&replace from [tsDate] to [TRDate].

It worked fine in my database and when I followed the steps in reverse it worked as well, so I'd double check the field name in your database, (& change the field name in each one of the 18 occurrences of it in the code to match), make sure that field [TRDate] is displayed on your form in it's own text box (otherwise the code will not know where to find it), and give it another go.

If all that doesn't work, I'm not sure what to recommend, since I'm not sure exactly how your forms are set up.

Let me know if you figure it out, it was a good exercise for me anyway, since I have a string of code to format a DateDiff like that now, but I'd like to see this work for you as well.

[JohnB47]

JohnB47,

I'm not sure what to tell you. That error message will come up if the field inside the brakets (ie. [TRDate]) is not the same as the field name in your database. If you put in the code and one of the instances of [TRDate] was misspelled, or the name was different in your database, then the view of the control from Form View would display #NAME?. When I put it in my database to test it, my field name was [tsDate], so I copied it into a blank MSWord document and did a find&replace from [tsDate] to [TRDate].

It worked fine in my database and when I followed the steps in reverse it worked as well, so I'd double check the field name in your database, (& change the field name in each one of the 18 occurrences of it in the code to match), make sure that field [TRDate] is displayed on your form in it's own text box (otherwise the code will not know where to find it), and give it another go.

If all that doesn't work, I'm not sure what to recommend, since I'm not sure exactly how your forms are set up.

Let me know if you figure it out, it was a good exercise for me anyway, since I have a string of code to format a DateDiff like that now, but I'd like to see this work for you as well.

I'm really sorry. Despite getting this right using the code in your first reply (and later too), I seem to have gone completely doolally after that. (getting field names all confused and then updating a query with new code but forgetting that my form was calling on a different query. Arrrrg). Perhaps I'm doing too many things at once. So, your code does work and very well too!

Many thanks for your time on this - it will be really useful to me, now that I know what I'm doing (up to a point).

Thank you.