Calculating Net Working Days in a VB module

  • Thread starter Kirk Justus via AccessMonster.com
  • Start date
K

Kirk Justus via AccessMonster.com

I'll begin with the statement that, "I am not a programmer." However, I do
work with Access and Excel daily, and have written some--albeit very
simplistic--VB modules.

Objective: My objective is to create a VB function module that I can use
to calculate the number of Working Days between two dates--total number of
days, less weekends and holidays.

Steps so far: Elsewhere on this site, I found referneces to other sites
were "A Couple of way to do it" could be found. The code I have so far is
below.

The problem is, I guess, that it was written for an earlier version of VB
and either the statements are no longer valid or the expected syntax is
different. I am using Access XP and it appears to be VB6.3.

The error occurs after I enter the function and run the query. But only
when I scroll over to the column where the function is used. The error is
"Compile Error - User-Defined type not defined" and it highlights the
everything in the after the "Dim" statement in this line: "Dim rst As
DAO.Recordset"

I would greatly appreciate any help someone could render.

--Kirk Justus

<pre>
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function
</pre>
 
G

Guest

Here is how we do ours, but it's for a Access Report.

Option Compare Database
Option Explicit
Const intWorkingHours = 8
Const intEndShift = 17
Const intStartShift = 8
Dim datStartDate As Date, datEndDate As Date, datStartTime
As Date, datEndTime As Date
Dim datStartingDate As Date, datEndingDate As Date,
datCurrent As Date
Dim datStartingTime As Date, datEndingTime As Date
Dim dblStartTime As Double, dblEndTime As Double
Dim dblWorkingHours As Double
Dim dblTempHour As Double, dblTempMin As Double,
dblTempTime As Double
Dim intDays As Integer
'TimePerSets Function
Dim dblSets As Double
Dim dblTotalMinutes As Double
Dim dblTotalSets As Double
'PlantAverageCycleTime Function

'PlantAverageTimePerSet Function
Dim dblAvgTimePerSet As Double
'ReportAverageCycleTime Function
Dim dblReportTotalMin As Double
Dim dblReportTotalSets As Double
'ReportTimePerSets Function
Dim dblReportAvgTimePerSet As Double
'Formating time
Dim strTime As String

Function HoursWorked(datStartDate, datStartTime,
datEndDate, datEndTime)
dblWorkingHours = 0

datCurrent = datStartDate
'Format the time to decimal
dblTempHour = Hour(datStartTime)
dblTempMin = Minute(datStartTime) / 60
dblStartTime = Format(dblTempHour + dblTempMin, "#.##")
'Format the time to decimal
dblTempHour = Hour(datEndTime)
dblTempMin = Minute(datEndTime) / 60
dblEndTime = Format(dblTempHour + dblTempMin, "#.##")

'Select case for the number of days
intDays = datEndDate - datStartDate + 1

Select Case intDays
Case Is = 1
If dblStartTime <= 12 And dblEndTime <= 12 Then
dblTempTime = Format(dblStartTime - 8, "#.##")
dblTempTime = Format(dblTempTime + (12 -
dblEndTime), "#.##")
dblWorkingHours = 4 - dblTempTime
ElseIf dblStartTime >= 13 And dblEndTime >= 13 Then
dblTempTime = Format(dblStartTime - 13, "#.##")
dblTempTime = Format(dblTempTime + (17 -
dblEndTime), "#.##")
dblWorkingHours = 4 - dblTempTime
Else
dblWorkingHours = Format(12 - dblStartTime, "#.##")
dblWorkingHours = dblWorkingHours + Format
(dblEndTime - 13, "#.##")
End If
Case Is = 2
If dblStartTime <= 12 Then
dblWorkingHours = Format(12 - dblStartTime, "#.##")
dblWorkingHours = dblWorkingHours + 4
Else
dblWorkingHours = Format(17 - dblStartTime, "#.##")
End If
If dblEndTime <= 12 Then
dblWorkingHours = dblWorkingHours + Format
(dblEndTime - 8, "#.##")
Else
dblWorkingHours = dblWorkingHours + Format
(dblEndTime - 13, "#.##")
dblWorkingHours = dblWorkingHours + 4
End If
Case Is > 2
Do
If datCurrent = datStartDate Then
If dblStartTime <= 12 Then
dblWorkingHours = Format(12 -
dblStartTime, "#.##")
dblWorkingHours = dblWorkingHours + 4
Else
dblWorkingHours = Format(17 -
dblStartTime, "#.##")
End If
Else
If WeekDay(datCurrent) <> 1 And WeekDay
(datCurrent) <> 7 Then
dblWorkingHours = dblWorkingHours + 8
End If
End If
datCurrent = datCurrent + 1
Loop Until datCurrent = datEndDate

If dblEndTime <= 12 Then
dblWorkingHours = dblWorkingHours + Format
(dblEndTime - 8, "#.##")
Else
dblWorkingHours = dblWorkingHours + Format
(dblEndTime - 13, "#.##")
dblWorkingHours = dblWorkingHours + 4
End If
End Select

dblTempHour = Fix(dblWorkingHours)
dblTempMin = (dblWorkingHours - dblTempHour) * 60

If dblTempMin <> 0 Then
dblTempMin = Format(dblTempMin, "#")
End If

Call FormatTime(dblTempHour, dblTempMin)
HoursWorked = strTime

End Function

Function TimePerSet(dblSets)
'Convert hours to minutes
dblTempMin = dblTempMin + (dblTempHour * 60)

'Add minutes and sets to use when determining plant
averages
dblTotalMinutes = dblTotalMinutes + dblTempMin
dblTotalSets = dblTotalSets + 1
'add minutes and sets to use when determining report
averages
dblReportTotalMin = dblReportTotalMin + dblTempMin
dblReportTotalSets = dblReportTotalSets + 1

dblTempMin = Format(dblTempMin / dblSets, "#")

'Used to calculate average set time per plant
dblAvgTimePerSet = dblAvgTimePerSet + dblTempMin
dblReportAvgTimePerSet = dblReportAvgTimePerSet +
dblTempMin

If dblTempMin < 60 Then
dblTempHour = 0
Else
dblTempMin = Format(dblTempMin / 60, "#.##")
dblTempHour = Fix(dblTempMin)
dblTempMin = (dblTempMin - dblTempHour) * 60
If dblTempMin <> 0 Then
dblTempMin = Format(dblTempMin, "#")
End If
End If

Call FormatTime(dblTempHour, dblTempMin)
TimePerSet = strTime

End Function

Function PlantAverageCycleTime()
dblTempMin = Format(dblTotalMinutes / dblTotalSets, "#")
If dblTempMin < 60 Then
dblTempHour = 0
dblTempMin = Format(dblTempMin, "#")
Else
dblTempMin = Format(dblTempMin / 60, "#.##")
dblTempHour = Fix(dblTempMin)
dblTempMin = (dblTempMin - dblTempHour) * 60
If dblTempMin <> 0 Then
dblTempMin = Format(dblTempMin, "#")
End If
End If

Call FormatTime(dblTempHour, dblTempMin)
PlantAverageCycleTime = strTime

dblTotalMinutes = 0
End Function

Function PlantAverageSetTime()
dblAvgTimePerSet = dblAvgTimePerSet / dblTotalSets
dblAvgTimePerSet = Format(dblAvgTimePerSet, "#")

If dblAvgTimePerSet < 60 Then
dblTempHour = 0
dblTempMin = dblAvgTimePerSet
Else
dblTempMin = Format(dblAvgTimePerSet / 60, "#.##")
dblTempHour = Fix(dblTempMin)
dblTempMin = (dblTempMin - dblTempHour) * 60
If dblTempMin <> 0 Then
dblTempMin = Format(dblTempMin, "#")
End If
End If

Call FormatTime(dblTempHour, dblTempMin)
PlantAverageSetTime = strTime

dblAvgTimePerSet = 0
dblTotalSets = 0
End Function

Function ReportTotalTime()
If dblReportTotalMin < 60 Then
dblTempHour = 0
dblTempMin = dblReportTotalMin
Else
dblTempMin = Format(dblReportTotalMin / 60, "#.##")
dblTempHour = Fix(dblTempMin)
dblTempMin = (dblTempMin - dblTempHour) * 60
If dblTempMin <> 0 Then
dblTempMin = Format(dblTempMin, "#")
End If
End If

Call FormatTime(dblTempHour, dblTempMin)
ReportTotalTime = strTime

dblReportTotalMin = 0
End Function
Function ReportAverageCycleTime()
dblTempMin = Format(dblReportTotalMin /
dblReportTotalSets, "#")
If dblTempMin < 60 Then
dblTempHour = 0
Else
dblTempMin = Format(dblTempMin / 60, "#.##")
dblTempHour = Fix(dblTempMin)
dblTempMin = (dblTempMin - dblTempHour) * 60
If dblTempMin <> 0 Then
dblTempMin = Format(dblTempMin, "#")
End If
End If

Call FormatTime(dblTempHour, dblTempMin)
ReportAverageCycleTime = strTime

End Function

Function ReportAverageSetTime()
dblReportAvgTimePerSet = dblReportAvgTimePerSet /
dblReportTotalSets
dblReportAvgTimePerSet = Format
(dblReportAvgTimePerSet, "#")
If dblReportAvgTimePerSet < 60 Then
dblTempHour = 0
dblTempMin = dblReportAvgTimePerSet
Else
dblTempMin = Format(dblReportAvgTimePerSet /
60, "#.##")
dblTempHour = Fix(dblTempMin)
dblTempMin = (dblTempMin - dblTempHour) * 60
If dblTempMin <> 0 Then
dblTempMin = Format(dblTempMin, "#")
End If
End If

Call FormatTime(dblTempHour, dblTempMin)
ReportAverageSetTime = strTime

dblReportAvgTimePerSet = 0
dblReportTotalSets = 0
End Function

Sub FormatTime(dblTempHour, dblTempMin)
If dblTempHour = 0 Then
strTime = dblTempMin & " Min."
ElseIf dblTempMin = 0 Then
strTime = dblTempHour & " Hrs."
Else
strTime = dblTempHour & " Hrs. " & dblTempMin & " Min."
End If
End Sub
 
D

Douglas J. Steele

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.x
Library while you're at it.
 
F

freedomjoe

I Followed your directions but VB is not recognizing the lines:

Dim rst As DAO Recordset
Dim DB As DAO Database
 
D

Douglas J. Steele

In that case, you must not have followed my instructions correctly. If
you've got a checkmark beside the reference for Microsoft DAO 3.6 Object
Library, those two lines should be recognized.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top