Working Days

[Guest]

How is it possible to calculate the working days (Mon to
Fri) in a select qurey given a start & end date.

I have a user defined function to calculate this in a
query as i cant fin a solution using the general
functions.
My only problem here is that I am unable to use agregate
funtions on the field containing the work day function I
have created.
Any help would be apreciated.

My function is as follows.

Function Work_Days(BegDate As Variant, EndDate As
Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

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
End Function

 

[Steve Schapel]

I can see no reason why you can't use aggregate functions on the result
of a calculated field using the Work_Days function. Can you give an
example of where you have experienced difficulty?

 

[Ian Tranter]

Steve
Thanks for the advice, Things look different after a good
brake & a nice cup of coffee.
I have re looked at my query & you are correct.
I inadvertently place the count aggregate function on the
total line under the date complete field hence my work
day's function had no date to compare.
I have re done the queries & all works fine!! ))))

 

[hermie]

I try to do the same but not got it work?

I put next formula in my query:

TotalDays: Work_Days([begdate]-[enddate])

What is wrong with this formula?

Herman

 

[Duane Hookom]

Try use a comma:
TotalDays: Work_Days([begdate], [enddate])


--
Duane Hookom
MS Access MVP

I try to do the same but not got it work?

I put next formula in my query:

TotalDays: Work_Days([begdate]-[enddate])

What is wrong with this formula?

Herman

Steve
Thanks for the advice, Things look different after a good
brake & a nice cup of coffee.
I have re looked at my query & you are correct.
I inadvertently place the count aggregate function on the
total line under the date complete field hence my work
day's function had no date to compare.
I have re done the queries & all works fine!! ))))