Counting years and months between dates

[jpr]

Friends, I have a form with four fields, date1, date2, date3 and date4.

All these have all a mm/dd/yyyy format and have their source to a
table.

I need to add an unbound control (I will name it txtresult) which will
count in months and years the difference between the dates.

Ex.
date1: 01/01/1970
date2: 05/05/1980

date3: 06/06/1990
date4: 06/30/2001

txtresult: .....

Fields date3 and date4 may be blank, therefore the code should consider
this option in counting only the first two.

Thanks for any help.

[Anthony England]

"jpr" <jp***@tin.it> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Friends, I have a form with four fields, date1, date2, date3 and date4.

All these have all a mm/dd/yyyy format and have their source to a
table.

I need to add an unbound control (I will name it txtresult) which will
count in months and years the difference between the dates.

Ex.
date1: 01/01/1970
date2: 05/05/1980

date3: 06/06/1990
date4: 06/30/2001

txtresult: .....

Fields date3 and date4 may be blank, therefore the code should consider
this option in counting only the first two.

Thanks for any help.

Create a sub to show this interval and like your previous post, you need to
call it from the OnCurrent event and also any place where you can catch the
dates changing. Here is the sort of thing:

Private Sub ShowInterval()

On Error GoTo Err_Handler

Dim lngDays As Long
Dim lngMonths As Long
Dim lngYears As Long
Dim strInterval As String
Dim dteOne As Date
Dim dteTwo As Date

If Not IsNull(Me.txtDate4) Then
If IsNull(Me.txtDate3) Then
strInterval = "Date 3 is missing"
Else
dteOne = Me.txtDate3.Value
dteTwo = Me.txtDate4.Value
If dteOne > dteTwo Then
strInterval = "Date four is before date three"
End If
End If
Else
If IsNull(Me.txtDate1) Then
If IsNull(Me.txtDate2) Then
strInterval = "Dates 1 and 2 are missing"
Else
strInterval = "Date 1 is missing"
End If
Else
If IsNull(Me.txtDate2) Then
strInterval = "Date 1 is missing"
Else
dteOne = Me.txtDate1.Value
dteTwo = Me.txtDate2.Value
If dteOne > dteTwo Then
strInterval = "Date two is before date one"
End If
End If
End If
End If

If Len(strInterval) = 0 Then

lngMonths = DateDiff("m", dteOne, dteTwo)

lngDays = DateDiff("d", DateAdd("m", lngMonths, dteOne), dteTwo)

If lngDays < 0 Then
lngMonths = lngMonths - 1
lngDays = DateDiff("d", DateAdd("m", lngMonths, dteOne), dteTwo)
End If

lngYears = lngMonths \ 12

lngMonths = lngMonths Mod 12

strInterval = CStr(lngYears) & " yrs " & _
CStr(lngMonths) & " mths " & _
CStr(lngDays) & " days"

End If

Me.txtResult = strInterval

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[jpr]

What can I say! Just perfect! Greate job.Working the way I want,
only,,,,I would like to remove the days. Since it is not necessary for
my case. Can I just show the yy and mm? Thanks.

[jpr]

Just forgot an important detail. I have added your code to the Open
event of the form since the OnCurrect already has code.

I will also need the same code for another group of controls meaning
that I will need to repeat the same for other fields. Where do I put
the code (I understand that i will simply change the txtnames with the
new ones). Thanks.

[Anthony England]

"jpr" <jp***@tin.it> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

What can I say! Just perfect! Greate job.Working the way I want,
only,,,,I would like to remove the days. Since it is not necessary for
my case. Can I just show the yy and mm? Thanks.

strInterval = CStr(lngYears) & " yrs " & _
CStr(lngMonths) & " mths " & _
CStr(lngDays) & " days"

Of course, just format the string however you want, e.g. change

strInterval = CStr(lngYears) & " yrs " & _
CStr(lngMonths) & " mths " & _
CStr(lngDays) & " days"

to:

strInterval = CStr(lngYears) & " years " & _
CStr(lngMonths) & " months"

or whatever you want.

[Anthony England]

"jpr" <jp***@tin.it> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Just forgot an important detail. I have added your code to the Open
event of the form since the OnCurrect already has code.
Do you think that you can't (or shouldn't) have more than one bit of code in
an event? This is not correct. If the code belongs in the OnCurrent event,
then that is where it should be! You can structure your code like this:

Private Sub DoThingOne()
MsgBox "Thing One"
End Sub

Private Sub DoThingTwo()
MsgBox "Thing Two"
End Sub

Private Sub Form_Current()
DoThingOne
DoThingTwo
End Sub

I will also need the same code for another group of controls meaning
that I will need to repeat the same for other fields. Where do I put
the code (I understand that i will simply change the txtnames with the
new ones). Thanks.

You could re-write the function so that one function could work for two sets
of controls, but it might not be worth the work. You could just structure
the code as I have shown above. Does that make sense?

[jpr]

Hello, thanks for your patience.
I have the feeling something is not working. I think it is only
counting one group of date fields. This is how I have placed the code :
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler
Dim lngDays As Long
Dim lngMonths As Long
Dim lngYears As Long
Dim strInterval As String
Dim dteOne As Date
Dim dteTwo As Date
If Not IsNull(Me.WEUSRFROM1) Then
If IsNull(Me.WEUSRTO1) Then
strInterval = "Date 3 is missing"
Else
dteOne = Me.WEUSRFROM1.Value
dteTwo = Me.WEUSRTO1.Value
If dteOne > dteTwo Then
strInterval = "Date four is before date three"
End If
End If
Else
If IsNull(Me.WEUSRFROM) Then
If IsNull(Me.WEUSRTO) Then
strInterval = "Dates 1 and 2 are missing"
Else
strInterval = "Date 1 is missing"
End If
Else
If IsNull(Me.WEUSRTO) Then
strInterval = "Date 1 is missing"
Else
dteOne = Me.WEUSRFROM.Value
dteTwo = Me.WEUSRTO.Value
If dteOne > dteTwo Then
strInterval = "Date two is before date one"
End If
End If
End If
End If
If Len(strInterval) = 0 Then
lngMonths = DateDiff("m", dteOne, dteTwo)
lngDays = DateDiff("d", DateAdd("m", lngMonths, dteOne),
dteTwo)
If lngDays < 0 Then
lngMonths = lngMonths - 1
lngDays = DateDiff("d", DateAdd("m", lngMonths, dteOne),
dteTwo)
End If
lngYears = lngMonths \ 12
lngMonths = lngMonths Mod 12
strInterval = CStr(lngYears) & " years " & _
CStr(lngMonths) & " months"


End If
Me.txtresult = strInterval
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Please note that I have four date fields and from what I understand
dates cannot show future dates (this is fine for me). I have placed
these dates in my form:

weusrfrom: 01/01/2000
weusrto: 02/01/2001
weusrfrom1: 01/01/2002
weusrto1: 02/01/2005
I get: 3 yrs 1mth.

How about placing additional fields named:
clusrfrom
clusrto
clusrfrom1
clusrto1

???THanks.

[Anthony England]

"jpr" <jp***@tin.it> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Hello, thanks for your patience.
I have the feeling something is not working. I think it is only
counting one group of date fields. This is how I have placed the code :
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler
Dim lngDays As Long
Dim lngMonths As Long
Dim lngYears As Long
Dim strInterval As String
Dim dteOne As Date
Dim dteTwo As Date
If Not IsNull(Me.WEUSRFROM1) Then
If IsNull(Me.WEUSRTO1) Then
strInterval = "Date 3 is missing"
Else
dteOne = Me.WEUSRFROM1.Value
dteTwo = Me.WEUSRTO1.Value
If dteOne > dteTwo Then
strInterval = "Date four is before date three"
End If
End If
Else
If IsNull(Me.WEUSRFROM) Then
If IsNull(Me.WEUSRTO) Then
strInterval = "Dates 1 and 2 are missing"
Else
strInterval = "Date 1 is missing"
End If
Else
If IsNull(Me.WEUSRTO) Then
strInterval = "Date 1 is missing"
Else
dteOne = Me.WEUSRFROM.Value
dteTwo = Me.WEUSRTO.Value
If dteOne > dteTwo Then
strInterval = "Date two is before date one"
End If
End If
End If
End If
If Len(strInterval) = 0 Then
lngMonths = DateDiff("m", dteOne, dteTwo)
lngDays = DateDiff("d", DateAdd("m", lngMonths, dteOne),
dteTwo)
If lngDays < 0 Then
lngMonths = lngMonths - 1
lngDays = DateDiff("d", DateAdd("m", lngMonths, dteOne),
dteTwo)
End If
lngYears = lngMonths \ 12
lngMonths = lngMonths Mod 12
strInterval = CStr(lngYears) & " years " & _
CStr(lngMonths) & " months"


End If
Me.txtresult = strInterval
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Please note that I have four date fields and from what I understand
dates cannot show future dates (this is fine for me). I have placed
these dates in my form:

weusrfrom: 01/01/2000
weusrto: 02/01/2001
weusrfrom1: 01/01/2002
weusrto1: 02/01/2005
I get: 3 yrs 1mth.

How about placing additional fields named:
clusrfrom
clusrto
clusrfrom1
clusrto1

???THanks.

When you write "I get: 3 yrs 1mth", are you saying this is correct or
incorrect?

To deal with the new set of controls you could either re-write the sub so it
takes the names of the controls as parameters, and then 1 sub could cope
with two different possiblities. But you could just copy and paste the sub
and change the names of the controls. The code should be written in their
own separate subs. These subs can then be called from multiple places like
the form's current event:

Private Sub DoControls1()
' Code for controls: weusrfrom, weusrto, weusrfrom1, weusrto1
End Sub

Private Sub DoControls2()
' Code for controls: clusrfrom, clusrto, clusrfrom1, clusrto1
End Sub

Private Sub Form_Current()
DoControls1
DoControls2
End Sub

[jpr]

Yes, I get 3 yrs 1 mth. It appears that is is only counting the first
two group of date fields.

[jpr]

I only counts two group of dates. Date3 and Date4. If you want I can
sent you a zip mdb file. Thanks.

[Anthony England]

"jpr" <jp***@tin.it> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I only counts two group of dates. Date3 and Date4. If you want I can
sent you a zip mdb file. Thanks.

That's what I thought *should* happen. In your original post you wrote:

"Fields date3 and date4 may be blank, therefore the code should consider
this option in counting only the first two."

So what should happen if all four dates are there? Have a look at the first
bit of the code - this is where I decide which two dates to compare. Can
you edit this yourself? If not, just give a clear explanation of what
should happen - If you are still stuck, I'll send you an e-mail address to
send any file.
If Not IsNull(Me.txtDate4) Then
If IsNull(Me.txtDate3) Then
strInterval = "Date 3 is missing"
Else
dteOne = Me.txtDate3.Value
dteTwo = Me.txtDate4.Value
If dteOne > dteTwo Then
strInterval = "Date four is before date three"
End If
End If
Else
If IsNull(Me.txtDate1) Then
If IsNull(Me.txtDate2) Then
strInterval = "Dates 1 and 2 are missing"
Else
strInterval = "Date 1 is missing"
End If
Else
If IsNull(Me.txtDate2) Then
strInterval = "Date 1 is missing"
Else
dteOne = Me.txtDate1.Value
dteTwo = Me.txtDate2.Value
If dteOne > dteTwo Then
strInterval = "Date two is before date one"
End If
End If
End If
End If

[jpr]

Thanks for your great help.
I will try to be more clear possible.

My form has 5 fields: four date fields and a txtresult.

I use the date fields to count the number of years a person has lived
in a certain Country, meaning that his residence can show intervals of
years (like from 2000 to 2002 and from 2004 to 2005).
If a person has resided for a consecutive number of years, well I will
only use the first two date fields. If not, I will need to complete the
other two date fields.

The txtresult field should consider both options.
1) Count only the first two if the date3 and date4 are blank.
2) Count all four date fields if all are showing dates.

Hope this helps.

by the way where are you from. I am from Italy.
Ciao.

[Anthony England]

"jpr" <jp***@tin.it> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Thanks for your great help.
I will try to be more clear possible.

My form has 5 fields: four date fields and a txtresult.

I use the date fields to count the number of years a person has lived
in a certain Country, meaning that his residence can show intervals of
years (like from 2000 to 2002 and from 2004 to 2005).
If a person has resided for a consecutive number of years, well I will
only use the first two date fields. If not, I will need to complete the
other two date fields.

The txtresult field should consider both options.
1) Count only the first two if the date3 and date4 are blank.
2) Count all four date fields if all are showing dates.

Hope this helps.

by the way where are you from. I am from Italy.
Ciao.

OK now I understand. I will have to re-write it. I will try and do it
today.

[jpr]

Hello Anthony, still willing to help me????Thanks.

[Anthony England]

"jpr" <jp***@tin.it> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hello Anthony, still willing to help me????Thanks.

I have sent a sample database in a private e-mail.