Query/Report Question

[mh]

I am creating a construction diary database and I am having some difficulty
calculating my number of working days, and would appreciate any insight into
what I need to do.

I have a fixed beginning date (notice to proceed date) that I have in a
table related to other project information. In a data entry form I have a
check box to be used if the work day will not be counted (noworkday).

I know the formula for the will be

working days = report date - beginning date + sum of noworkday.

My report will be based on a date range, so how can get the sum of noworkday
for each date of the report and have the working days calculate correctly?

 

[Jeff Boyce]

There are any number of suggested approaches listed at Google.com if you
search on "MS Access working days". Instead of requiring the user to enter
"noworkday" information, you could just use one of those functions to
calculate the number of working days between one date and another.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[kagard]

Here is code from the Access 2007 help file:

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

On Error GoTo Err_Work_Days

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0

Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop

Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

' If either BegDate or EndDate is Null, return a zero
' to indicate that no workdays passed between the two dates.

If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
' If some other error occurs, provide a message.
MsgBox "Error " & Err.Number & ": " & Err.Description
End If

End Function

Keith

 

[MH]

I do not think I am making myself clear in what I am looking.


The noworkday variable I am trying to sum are days that are typical workdays
that are not counted due to weather/soil or other project conditions.

Example. Monday (workday #1), Tuesday (workday #2), Wednesday (no work
rain), Thursday (workday #3), Friday (workday #4) etc.

 

[Jeff Boyce]

Perhaps you don't need to "number" the workdays in your table. You can use
a query to do that.

If your table (it all starts with the data) had a field (?Yes/No) that was
used to indicate a "workday" (or a "nonworkday"), that might be the
simplest. If you need to know why a day was a nonworkday, you could use a
text field in the table instead... if there's something in that text field,
it would be the reason a day was NOT a workday (e.g., "rain", "soil",
"equipment", ...)

Regards

Jeff Boyce
Microsoft Office/Access MVP