Counting years and months between dates

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.

re: Counting years and months between dates

"jpr" <jprma@tin.it> wrote in message
news:1138787341.788789.99150@z14g2000cwz.googlegro ups.com...[color=blue]
> 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.[/color]


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

re: Counting years and months between dates

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.

re: Counting years and months between dates

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.

re: Counting years and months between dates

"jpr" <jprma@tin.it> wrote in message
news:1138799379.389335.165320@g43g2000cwa.googlegr oups.com...[color=blue]
> 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.[/color]


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.

re: Counting years and months between dates

"jpr" <jprma@tin.it> wrote in message
news:1138800626.175539.253910@z14g2000cwz.googlegr oups.com...[color=blue]
> Just forgot an important detail. I have added your code to the Open
> event of the form since the OnCurrect already has code.[/color]

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

[color=blue]
> 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.[/color]

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?

re: Counting years and months between dates

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.

re: Counting years and months between dates

"jpr" <jprma@tin.it> wrote in message
news:1138805499.269358.232780@g43g2000cwa.googlegr oups.com...[color=blue]
> 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.[/color]



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

re: Counting years and months between dates

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

re: Counting years and months between dates

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

re: Counting years and months between dates

"jpr" <jprma@tin.it> wrote in message
news:1138864193.144472.179750@z14g2000cwz.googlegr oups.com...[color=blue]
>I only counts two group of dates. Date3 and Date4. If you want I can
> sent you a zip mdb file. Thanks.[/color]


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

re: Counting years and months between dates

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.

re: Counting years and months between dates

"jpr" <jprma@tin.it> wrote in message
news:1138877359.182483.262990@g47g2000cwa.googlegr oups.com...[color=blue]
> 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.[/color]


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

re: Counting years and months between dates

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

re: Counting years and months between dates

"jpr" <jprma@tin.it> wrote in message
news:1139295415.087425.281730@g47g2000cwa.googlegr oups.com...[color=blue]
> Hello Anthony, still willing to help me????Thanks.[/color]


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