Exact Date difference in Years,months and Days to be show in Form Field.

[s_ojha]

I have a form in which Date of Birth is being entered in DOB field. There is another Field/column in the Form i.e. Age in which I want Date difference in Years, Months and Days to be displayed automatically in Age Field as soon as Date of Birth is entered. For eg. Date of Birth is 24/11/1975 and I want date difference on a particular date say 31/01/2009 for all employees. This was easy in Excel by using formula DATEDIF. How could I do the same in MS Access 2000 or 2007. Alternatively, in Query or Report also it can be used. I have recruitment table of 10,000 candidates and doing it manually will take a lot of time. Please help.

 

[s_ojha]

Re: Exact Date difference in Years,months and Days to be shown in Form Field.

I think to find exact age i.e. Years, months and days is very hard in Access. Only Age or Month or Days can be calculated individually.

 

[alansidman]

You may be right. I had one more idea on the issue and I have gotten close, but leap years seem to mess it up. Here is what I came up with and I was able to nail the years and months, but the days are off due to leap years that have passed since the birthdate. Perhaps you can fool with the mathematics surrounding the leap years to make it work.

Years: Years: Abs(DateDiff("d",Date(),[Bdate])\365.25)
Note the reverse slash here to give you the interger for the division

Months:Months: Abs(DateDiff("m",Date(),[Bdate]) Mod 12)

Days: Days: Abs(DateDiff("d",Date(),[bdate]) Mod [Years])
This formula because of leap years are off. I tried playing with subtracting the number of leap years since the birth date, but that does not seem to work.

Alan

 

[alansidman]

Found this and thought it might be your answer.

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=215

Alan

 

[s_ojha]

Re: Exact Date difference in Years,months and Days to be shown in Form Field.

Thanks Alansidman Sir. I was trying your formula. The years and months came correct, but while trying days it came into error/#Name. I could not understand for what and where you have used [years] while calculating Days. However, I am going to modify my Form to get the desired result as per RoggersAccessLibrary Sample. This will be a long process. However, if your formula works it will be great. So, please continue on trying. I am of the belief you will get the desired result/output. Robers technique is very long but will be an alternative to go on. Anyway infinite thanks to you.

 

[alansidman]

[Years] is the name of the field created in the first formula. Use whatever field name you used for this calculation. In queries, you can change the expr1: to whatever name you wish for it to appear as so long as you follow it with a colon.

Alan

 

[s_ojha]

Re: Exact Date difference in Years,months and Days to be shown in Form Field.

Alansidman Sir, I tried your formula and reached exactly very near. However, I have not been able to round the days. I am submitting you the formula. Could you please help me so that I could round the days. As regards Leap Years 01 extra day does not matter much OR I will query the leap years record from the database and calculate their difference by applying 366 days. The formula I have used is below :-

=Abs(DateDiff("d",#30/06/2008#,[Date of Birth])\365.25) & "Years," & Abs(DateDiff("m",#30/06/2008#,[Date of Birth]) Mod 12) & "Months" & Abs(DateDiff("d",#30/06/2008#,[Date of Birth]) Mod #30/06/2008#)/(365) & "Days"

Please help me in rounding the days and predict whether this will give correct output for every records ignoring Leap Year factor.

 

[alansidman]

It might be easier for you to just take Rogers example that I sent to you and look at the VBA code behind the unbound text boxes. If you are unsure how to get to the code, Open the form in Design mode and then press the Alt Key and the F11 key at the same time. This will open the VBA window and let you look at the code supporting each of the text boxes. You can then adapt it to your own needs. Why try and reinvent the "wheel" when it already exists.

Alan

 

[s_ojha]

Re: Exact Date difference in Years,months and Days to be shown in Form Field.

Alan Sir. Ultimately I have reached to the correct solution. I have used the RogersAccessLibrary2K Technique/formula and have concanetated the Years, months & days formulas substituting my fields/ values. The concatenation formula I have used is:-

=Int(DateDiff("m",[Date of Birth],#30/06/2008#)/12) & " Years, " & IIf(DatePart("d",#30/06/2008#)-DatePart("d",[Date of Birth])>=0,DateDiff("m",[Date of Birth],#30/06/2008#)-(Int(DateDiff("m",[Date of Birth],#30/06/2008#)/12)*12),DateDiff("m",[Date of Birth],#30/06/2008#)-(Int(DateDiff("m",[Date of Birth],#30/06/2008#)/12)*12)) & " months, " & IIf(DatePart("d",#30/06/2008#)-DatePart("d",[Date of Birth])>=0,DateDiff("d",DateAdd("m",DateDiff("m",[Date of Birth],#30/06/2008#),[Date of Birth]),#30/06/2008#),DateDiff("d",DateAdd("m",DateDiff("m",[Date of Birth],#30/06/2008#)-1,[Date of Birth]),#30/06/2008#)) & " Days"

Infinite Thanks. I think my work is over. Again Thanks.

 

[zyborgmonkey]

Re: Exact Date difference in Years,months and Days to be shown in Form Field.

Thanks guys spent ages trying to sort this out.
Made some slight changes to suit my needs, no doubt it would be cleaner in VBA.

I'm using it for an employment record. The current employment will have a blank "DateTo" there is the option of setting default to Date() but I want it shown blank for employment still running so I've added this in to cover it:

iif(isnull([DateTo]),Date(),[DateTo])


Combined Below.


=Int(DateDiff("m",[DateFrom],iif(isnull([DateTo]),Date(),[DateTo]))/12) & " Y: " & IIf(DatePart("d",iif(isnull([DateTo]),Date(),[DateTo]))-DatePart("d",[DateFrom])>=0,DateDiff("m",[DateFrom],iif(isnull([DateTo]),Date(),[DateTo]))-(Int(DateDiff("m",[DateFrom],iif(isnull([DateTo]),Date(),[DateTo]))/12)*12),DateDiff("m",[DateFrom],iif(isnull([DateTo]),Date(),[DateTo]))-(Int(DateDiff("m",[DateFrom],iif(isnull([DateTo]),Date(),[DateTo]))/12)*12)) & " M " & IIf(DatePart("d",iif(isnull([DateTo]),Date(),[DateTo]))-DatePart("d",[DateFrom])>=0,DateDiff("d",DateAdd("m",DateDiff("m",[DateFrom],iif(isnull([DateTo]),Date(),[DateTo])),[DateFrom]),iif(isnull([DateTo]),Date(),[DateTo])),DateDiff("d",DateAdd("m",DateDiff("m",[DateFrom],iif(isnull([DateTo]),Date(),[DateTo]))-1,[DateFrom]),iif(isnull([DateTo]),Date(),[DateTo]))) & " Days"



I'm thinking also perhaps to calculate out the zero values and their suffix when years or months = 0 but that's more faffing for another time