In access in one field I have one date and in another field I have another date. I want to calculate the number of years.,months and days elapsed between these two date. e.g. suppose in one field I have 30.06.2005 and in other 20.04.2004 then it should show 1 year 2 months and 10 days.
Calculating Difference between dates (1 Viewer)
- Thread starter ananga
- Start date
ColinEssex
Old registered user
- Local time
- Today, 20:47
- Joined
- Feb 22, 2002
- Messages
- 9,116
Check out DateDiff() function.
Col
Col
Thank you for supplying the sample database
I am a newbie with Access but an oldie in age.
The sample code works the way that I want but how can I write the values into separate fields called say VMonth,VDay.
I have two fields called StartDate and EndDate and I want to calculate the difference in months and years.
Thanks in antipication.
I am a newbie with Access but an oldie in age.
The sample code works the way that I want but how can I write the values into separate fields called say VMonth,VDay.
I have two fields called StartDate and EndDate and I want to calculate the difference in months and years.
Thanks in antipication.
Sorry for the misunderstanding
The database works fine for me.
However I want to extend the functionality by having the values in the years months and days text boxes in the form. I was wanting to use these values for other uses so i was wanting to write these values to designated fields in the table. Say I have fields in the table called vYears, Vmonths and Vdays and the values in your text boxes are written to those fields when I press a command button called "Save Record".
Any ideas.
Thanks.
The database works fine for me.
However I want to extend the functionality by having the values in the years months and days text boxes in the form. I was wanting to use these values for other uses so i was wanting to write these values to designated fields in the table. Say I have fields in the table called vYears, Vmonths and Vdays and the values in your text boxes are written to those fields when I press a command button called "Save Record".
Any ideas.
Thanks.
ananga said:
Hi there,
You can easily break apart the date field with the functions Month(), Day(), Year(). So to get the breakdown of elapsed years, months, days add these three fields to your query: Year(DateField1) - Year(DateField2), Month(DateField1) - Month(DateField2), Day(DateField1) - Day(DateField2).
hehe, you are right of course....I did not think this through entirely. I guess it's been tackled already but had I attempted this in a more thoughtful manner I might've gone about using lots of Iif's and nested Iif statements. First there would be a test for Leap year, because if there is a leap year involved you would be dividing the difference of the two date fields by 366 instead of 365, and then taking the floor of that to get years elapsed.
... I forsee many problems with that approach, so I would have ended up asking for help from the pros here!
... I forsee many problems with that approach, so I would have ended up asking for help from the pros here!
