date difference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone tell me how i couldfind the difference between two date in years
and months in access.
one field is named start date and i would like it to give me the years and
months. for example my start date is 11/13/89 i have 16 years 3 months.

Thank you
 
If you have 2003, just create a new database and import all the objects from
my 97 mdb into it. (thre are just a couple of objects to import)
 
Hi ssddteam,

See Below:

' Declare variables.
Dim dTheNewDate As Date
Dim sMsg As String
Dim nYears As Integer
Dim nMonths As Integer
'Get the total months
'dTheDate = #11/13/1989#
nMonths = DateDiff("m", dTheDate, Now)
'Calculate the years
nYears = nMonths \ 12
'Calculate the remaining months
nMonths = nMonths - (nYears * 12)
'Build the message
sMsg = nYears & " Years and " & nMonths & _
" Months from " & dTheDate
'Display the message
MsgBox sMsg
 
Hi ssddteam,

OOPS, my previous code was wrong, see modified code below

' Declare variables.
Dim sMsg As String
Dim nYears As Integer
Dim nMonths As Integer
' Define the start date
dTheDate = #11/13/1989#
'Get the total months
nMonths = DateDiff("m", dTheDate, Now)
'Calculate the years
nYears = nMonths \ 12
'Calculate the remaining months
nMonths = nMonths - (nYears * 12)
'Build the message
sMsg = nYears & " Years and " & nMonths & _
" Months from " & dTheDate
'Display the message
MsgBox sMsg
 
Well, I went a different route than writing code since I'm mostly new to
Access. This is a roundabout way of getting it without having to deal with
all of the code.
Instead of having it all be one field, I decided to seperate them out to
three fields: Years, Months and Days
In the form, I created 8 new unbound boxes (you only need 6 if you don't
want the days, but I was figuring out how many days until a lease was up, so
I need the days). With you wanting the Start Date, have as my Expiration
Date, so swich as needed.
After that, I labeled the boxes: YearsLeft, NewYear, MonthsLeft, NewMonth
and DaysLeft. The other three boxes don't need naming at this time.
I'm working towards a date and if you're working away from it, switch the
two dates around. But here are what I put for those fields under Control
Source:
YearsLeft - =DateDiff("yyyy",Date(),[Expiration])
NewYear - =DateAdd("yyyy",[YearsLeft],Date())
MonthsLeft - =DateDiff("m",[NewYear],[Expiration])
NewMonth - =DateAdd("m",[MonthsLeft],[NewYear])
DaysLeft - =DateDiff("d",[NewMonth],[Expiration])
What you are doing is working slow to get those fields to tell you how much
inbetween (DateDiff), but, by adding the dates (DateAdd) you can find out
what it would be, slowly getting to the date you are looking for. Now, I hid
those fields (Since they really don't need to be shown) and the three reaming
fields I have Labeled Years, Days, Months, which those are visable.
Years - =[YearsLeft]
Months - =[MonthsLeft]
Days - =[DaysLeft]

Hope this helps a couple of people who don't want to write in alot of code.
 
Back
Top