access form

[Cristina]

I have an access form that calculates workdays excluding holidays keyed in a tabe, and weekends. When I put a date in 2011 it does not take into account the holidays. I am new at this and actually got this from the internet. Can someone please adjust the code so it takes into account dates in 2011 and future. Thanks
Option Compare Database
Option Explicit
Dim Response, Title, Prompt As String
Private Sub Form_Current()
TotalDaysBetweenStartStop = Null
TotalWorkDaysBetweenStartStop = Null
StartDate = Null
EndDate = Null
End Sub
Private Sub StartDate_AfterUpdate()
If IsNull(EndDate) Then
TotalDaysBetweenStartStop = Null
TotalWorkDaysBetweenStartStop = Null
DoCmd.GoToControl "EndDate"
ElseIf Not IsNull(EndDate) Then
CalculateWorkdays
End If
End Sub
Private Sub EndDate_AfterUpdate()
If IsNull(StartDate) Then
TotalDaysBetweenStartStop = Null
TotalWorkDaysBetweenStartStop = Null
DoCmd.GoToControl "StartDate"
Else
CalculateWorkdays
End If
End Sub
Private Sub CalculateWorkdays()
Dim TempDate As Date, WorkDays As Integer, Counter As Integer, AllDays As Integer
If IsNull(StartDate) Or IsNull(EndDate) Then ' ------------------ Check for Legitimate Dates?
Prompt = "Enter a Legitimate Start Date and End Date"
Title = "Invalid Date"
Exit Sub
End If
'*********** ************************************* Calculate ALL Days between StartDate and EndDate
AllDays = DateDiff("d", StartDate, EndDate)
TotalDaysBetweenStartStop = AllDays
'************************************************ Calculate WORK Days between StartDate and EndDate
WorkDays = 0
For Counter = 0 To AllDays
TempDate = StartDate + Counter
If IsNull(DLookup("[HolidayDate]", "tblHolidays", "HolidayDate = #" & TempDate & "#")) _
And WeekDay(TempDate) <> 7 _
And WeekDay(TempDate) <> 1 Then
WorkDays = WorkDays + 1
End If
Next Counter
TotalWorkDaysBetweenStartStop = WorkDays
End Sub

 

[PNGBill]

Your code doesn't seem to care what year you are in which is how it should be.

If IsNull(DLookup("[HolidayDate]", "tblHolidays", "HolidayDate = #" & TempDate & "#")) _

Here the code is looking at a table of holiday dates.

Have you checked this table (tblHolidays) to see if the 2011 dates are included?

 

[pbaldy]

Are the 2011 holidays entered in tblHolidays?

 

[Cristina]

Yes, I put a date of 3 Jan as a holiday, I ask for working days between 1/1/11 and 4/1/11 and it returns 2 days

 

[Cristina]

1st being a Saturday
2nd being a SUnday
3rd being a holiday, should return 1 day

 

[PNGBill]

Have you checked the similar data for 2010 and it would only have one date showing ?

 

[PNGBill]

try putting 1st march as a holiday - this will test if your date format is the problem and it reads 3rd Jan as 1st march and 1st march as 3rd jan.

 

[Cristina]

I works well for dates in 2010

 

[pbaldy]

Insert a breakpoint in the code and step through it so you can see exactly where it is getting the extra day. Or post it here so we can play with it.

 

[Cristina]

Thanks all. Tried shifting the date around and it worked. Grateful

 

[PNGBill]

If you mean 3/1/2011 is 1st Jan then you should think about the data in your Holidays table. Is this US date also?? if so, then no worries, just us US date from now on.

But.. if it is "normal" date and you would normally type 3rd January as 3/1/2011 then you can edit your vba code so it converts the table dates to mm/dd/yyy and then you can enter dates correctly (3/1/2011) in your table and all will be well.

This may be the code.

[SIZE=3]If IsNull(DLookup("[HolidayDate]", "tblHolidays", "HolidayDate = #" & Format(TempDate, "mm\/dd\/yyyy") & "#"))[/SIZE]

Then you enter that table data as 1/3/2011 and the code will read it as 3/1/2011