The dreaded leap year - desperate!

[hanosm]

I have a textbox (txtJobStartDate).

In order to calculate the amount of time the employee has been employed, i have another text box (txtYearsWorked) with the following control source:

=(DateDiff("d",[txtJobStartDate],[Date]()))/365

This gives me the amount of time worked as a decimal figure.

I then need to calculate whether today is an anniversary of the start date, so I need to check whether txtYearsWorked is a whole number. To do this, I have the following code in the OnCurrent section of the form:

Dim YearsWorkedWhole As Integer
Dim Remainder As Double

YearsWorkedWhole = CInt(Me.txtYearsWorked)
Remainder = Me.txtYearsWorked - YearsWorkedWhole
If Remainder = 0 Then
DoCmd.OpenForm "frmLengthOfEmploymentReminder"
End If

This works perfectly for 2000, 2001, 2002, but it doesn't work for 1999, 1998, etc.

For example

If txtJobStartDate = 24/09/00 Then txtYearsWorked = 3,

but if txtJobStartDate = 24/09/99 Then txtYearsWorked = 4.0027397260274 instead of 4.

The problem is the extra day in a leap year.

e.g. If JobStartDate = 24/09/00, then the number of days worked is 1095 days, which is divisible by 365 (=3).

But, If JobStartDate = 24/09/99, then the number of days worked is 1461 and not 1460 (as the year 2000 was a leap year). 1461 is not divisible by 365.

Any ideas on how to get around this? My head hurts.

 

[twonames]

Thinking out loud here...

Can't you use DateDiff("yyyy", ....etc to determine the number of years worked?

To determine if it is an anniversary can't you compare the day and month of the dates?

Hope it helps.

 

[hanosm]

Tried "yyyy". This always seems to give a whole number, so if the JobStartDate = 23/09/99, answer would be 4, as it would be is JobStartDate = 24/09/99, 25/09/99, 26/09/99, etc.

As for comparing day and month, i'm not sure how you implement this.

 

[twonames]

If you use Day(txtJobStartDate) to extract the day of the date and Month(txtJobStartDate) to extract the month, you can then do likewise with Date() and compare the two to determine if it's an anniversary.

 

[PeteJohnston]

DateDiff("yyyy",txtDate) works fine to get the number of years worked. Whether it is an an anniversary of the start date can be done as

If (Day(Now) = Day(txtDate)) And _
           (Month(Now) = Month(txtDate)) Then
    ' This is an anniversary of the start date
End If

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[MichaelRed]

There are numerous ways to do either the whole thing or the various parts.

Leap year can be 'tested' by checking if Feb 29, yyyy is a date. Also see if hte number of days between Jan 1, YYYY and Jan 1, YYYY + 1 is 366 (is leap Year.

Date serial can be used to check wheather a date is an aniversary. a (small) example:

Public Function basAnivDt(AnivDt As Date, Optional ChkDt As Variant) As Boolean

    Dim MyChkDt As Date

    If (IsMissing(ChkDt)) Then
        MyChkDt = Date
     Else
        MyChkDt = ChkDt
    End If

    If (Not IsDate(MyChkDt)) Then
        Exit Function
    End If

    If (AnivDt = DateSerial(Year(AnivDt), Month(MyChkDt), Day(MyChkDt))) Then
        basAnivDt = True
    End If

End Function


Of course these and other concepts can be combined in numerous ways to return the desired information.

? basAnivDt(#9/23/03#)
False
? basAnivDt(#9/24/99#)
True
? basAnivDt(#9/24/99#, #9/23/2006#)
False
? basAnivDt(#9/23/99#, #9/23/06#)
True


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BobJacksonNCI]

hanosm,

It is not mannerly to put the same post in multiple forums!
You wasted space and the time of several folks who volunteer their assistance.
(thread702-662822)

Please view the following thread.

HTH,
Bob
Thread181-473997 provides information regarding this site.

 

[hanosm]

Apologies.

But the help was not wasted!