Discussion Locked

VB Code

0 helpful
11 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

HELP! Can someone please help me figure out why the following module is reading weekend days as business days? It should calulate as 2 days instead of three.
Thanks!

Function BusinessDays(EDate, BDate) As Integer
Dim mydb As Database
Dim result As Recordset
Dim Wherestring As String
Dim checkdate As Date

Set mydb = CurrentDb
Set result = mydb.OpenRecordset("Select Hdate from Holidays")
If IsNull(EDate) Then Exit Function
checkdate = EDate
BizDays = 0

While checkdate > BDate
If Weekday(checkdate) > 1 And Weekday(checkdate) < 7 Then
Wherestring = "Hdate =#" & checkdate & "#"
With result
.MoveLast
.FindFirst Wherestring
If .NoMatch Then
BizDays = BizDays + 1
End If
End With
End If
checkdate = checkdate - 1
Wend
BusinessDays = BizDays

End Function
Function Days(EDate, BDate) As Integer
Dim mydb As Database
Dim result As Recordset
Dim Wherestring As String
Dim checkdate As Date

Set mydb = CurrentDb
Set result = mydb.OpenRecordset("Select Hdate from Holidays")
If IsNull(EDate) Then Exit Function
checkdate = EDate
BizDays = 0

While checkdate > BDate
Wherestring = "Hdate =#" & checkdate & "#"
With result
.MoveLast
.FindFirst Wherestring
If .NoMatch Then
BizDays = BizDays + 1 End If
End With
checkdate = checkdate - 1
Wend
Days = BizDays
End Function

11 replies

by glen_mcleod

E-mail me a copy of your 'Holidays' table, I'll find the problem.

err, Access97 or 2000? And I only need the table (not your entire DB) zipped please.

mcleod_glen@hotmail.com

by shmaltz

I dont understand what are trying to do with these functions please give more info.

by sankulpune

Hello,

Its really difficult to pinpoint the error unless one knows more details about for what each variable stands for and what exactly you are trying to achieve.

But my feeling is that the following statement might just be contributing to the error:

If .NoMatch Then
BizDays = BizDays + 1
End If

You may find out exactly by debugging your module stepwise and finding out values of relevant variables after execution of every statement with 'Auto Data Tips' on or in the Watch Window.

Your feedback will be highly appreciated.

With regards,

SanganakSakha
Simple & Effective Solutions To Small Problems, No QuickFixes

by mpdcsup

I've tested your code and it performs as written. You do realize that BDate is not included in the calculation (which would actually cause the error to go the other way by providing 2 when you were expecting 3).

by mpdcsup

Here are the results. The first number is Weekday() and number in parentheses is BizDays. 12/25/00 was in the database as a holiday.

12/28/00 : 5 (1)
12/27/00 : 4 (2)
12/26/00 : 3 (3)
12/25/00 : 2 (3)
12/24/00 : 1 (3)
12/23/00 : 7 (3)
12/22/00 : 6 (4)

by tlscruggs

I haven't had a chance yet to go back to that particular database yet. I'm currently working on other complex databases with more priority. I'll keep you guys posted. Thank you.

This discussion is closed to new replies.