Working with dates

[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("y",[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.

Any ideas????

 

[KenReay]

Hi

Before advancing a theory as to why, can I ask a question?

Why are you using the "y" parameter instead of the "d" parameter in DateDiff() ?

As I understood it y returns the number of days less weekends, ie the number of working days, but d returns the number of days, if I am right this does not seem to equate with your use of 365 ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[hanosm]

In all honesty, I can't remember why I am using "y" instead of "d"!

According to the help file, "y" equates to "Day of week" and "d" equates to "day". So I guess "y" counts the number of Mondays (for example) between the original date and now.

Anyway, I have changed the code to read "d" instead of "y", but the initial problem remains!

 

[hanosm]

OK, i know what the problem is, but I don't know how to solve it.

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.

 

[BobJacksonNCI]

How about...

If(DatePart("m",txtJobStartDate) = DatePart("m", date())) and (DatePart("d",txtJobStartDate) = DatePart("d", date())) then
DoCmd.OpenForm "frmLengthOfEmploymentReminder"
End If

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

 

[hanosm]

BOBJACKSONWCOM

Thankyou!

Works perfectly. I still need to know how many years have been worked, but I can fix that easily using your code!

Thanks again.

 

[BobJacksonNCI]

You're welcome.

DateDiff("yyyy", txtJobStartDate, date())

will give you the number of years.
Bob

 

[BobJacksonNCI]

Hello!

"Upon further review..."

The DateDiff calculates based on the year only, which returns number of years PLUS 1 if the anniversary date has not passed.
Instead, use:
Int(((Date()-[txtJobStartDate])+1)/365.2425)

Also, while the DatePart anniversary determination works, it does not allow any variation. If you want to include employees for a range of dates, there is a better way. For example, if you wanted to include folks for the previous two days (catch Sat/Sun anniversaries on Monday) and all of those for the upcoming 4 days (Get everyone for the week on Monday), you could do this:
Format([txtJobStartDate], "mmdd&quot Between Format(Date()-2,"mmdd&quot And Format(Date()+4,"mmdd&quot

HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.