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

s_ojha

Board Regular
Joined
Oct 19, 2004
Messages
92
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Re: Exact Date difference in Years,months and Days to be shown in Form Field.

This is not my solution. I want that the result should be shown combined as
For eg:- 15 Years, 10 month, 25 days. in a single column. Most probably if
it is shown in Form it is best or otherwise in Query or Report. Please help with
a proper example. The dates are in UK/Indian format.
 
Upvote 0
Re: Exact Date difference in Years,months and Days to be shown in Form Field.

that is a proper example
the DateDiff function calculates the number of years, days, hours, seconds, months between two dates


this is copied off that page
Code:
For example:

    DateDiff ("yyyy", #15/10/1998#, #22/11/2003#)     would return 5
    DateDiff ("m", #15/10/2003#, #22/11/2003#)     would return 1
    DateDiff ("d", #15/10/2003#, #22/11/2003#)     would return 38
 
Upvote 0
What actually I need is
For example:-
=DATEDIF(A1,$C$1,"y")&" Y, "& DATEDIF(A1,$C$1,"ym")& " M, " & DATEDIF(A1,$C$1,"md") & " D" as I use in Excel to be used in Access in Form

How to combine the above individually shown years, months and dates of your example for Access 2007 in single field/column to show in Form and what do you mean by copy off the page. Please explain.
 
Upvote 0
You would use the same concatenation concept in Access, making sure to use the Access formula syntax combining the three formulae as James has shown. If the calculation is in a query, add a new column and in the field name put the concatenation formula. If in a form, use an unbound text box to combine (concatenate) the results from the individual text boxes or use the query as your source for the text box.

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

Sorry Friends. This is not my actual solution. I am not getting the desired result. Let me show you an example.

Date of Birth - 05/09/1982
( Field containing Date of Birth of candidates)

AGE CALCULATION --
(As per Access 2007)
Age as on 30/06/2008 - = DateDiff("yyyy",[Date of Birth],#30/06/2008#) & " Yrs."
& DateDiff("m",[Date of Birth],#30/06/2008#) & "months"
& DateDiff("d",[Date of Birth],#30/06/2008#) & "Days"

The result as per above access syntax is 26 Yrs., 309months, 9430Days -- WRONG

(As per Excel 2007)
Age as on 30/06/2008 =DATEDIF(A1,"30/06/2008","y")&" Y, "
& DATEDIF(A1,$B$1,"ym")& " M, "
& DATEDIF(A1,$B$1,"md") & " D"

The result as per above Excel syntax is 25 Yrs., 09months, 25 Days -- CORRECT

I want the same result to be produced in Access 2007 FORM. In the Form I have a field which is "Age as on 30/06/2008" which should automatically display correct age as soon as Date of Birth is entered in Date of Birth Field i.e. age/result as like Excel.
 
Last edited:
Upvote 0
Relook at James' example. You did not follow his logic. Excel and Access do not use the same format for this formula. Wanting the same result will mean doing some manipulations (not hard) to achieve the desired results.
Alan
 
Last edited:
Upvote 0
Thought of an easier way. Three formulae that can be concatenated.

1. To determine the Years DatePart("yyyy",Date())-DatePart("yyyy",[bdate])
2. To determine the Months Abs(DatePart("m",Date())-DatePart("m",[bdate]))
3. To determine the Days Abs(DatePart("d",Date())-DatePart("d",[bdate]))

Where Date() represents today and bdate is the field with the birthday.

Good luck.

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

Alnsidman Sir. Thanks for your effort. But again sorry. Have you gone through the result. The output is not correct. By concatenating your above syntax also not provides correct result. Please calculate manually the exact difference i.e how much years, months and days has a person completed which is evident from example Date of Birth -- 05/09/1982 and to calculate on the date 30/06/2008. What result/output will you get from above access syntax is 26 Yrs., 03 months, 25 Days which is WRONG. The actual result/output is 25 Yrs., 09 months and 25 days. which is CORRECT. Please reconsider for a exact solution. I want to give exact date difference so as not to do the work manually of 10,000 records. Otherwise, I will have to revert back to Excel. Is there any way where I can use the formula of Excel in Access.

The concatenation syntax I have used is :-
=DatePart("yyyy",#30/06/2008#)-DatePart("yyyy",[Date of Birth]) & "Yrs.," & Abs(DatePart("m",#30/06/2008#)-DatePart("m",[Date of Birth])) & "Months," & Abs(DatePart("d",#30/06/2008#)-DatePart("d",[Date of Birth])) & "Days"
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top