business days

[Guest]

I can't get this to work. I'm a beginer-intermediate Access user and I've
gotten as far as creating the module with this code...What next? When I use
the expresion builder to use the function, it's not there. I see the name of
the module, but the function is not there. Please help.

Thanks.

Option Explicit

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

 

[Douglas J. Steele]

What did you name the module? If it's also Work_Days, rename it.

 

[Guest]

I named it DateFunction. My question is how do I use it in a query? When I
look up the function, I see the module name, but the function is not there.

 

[Douglas J. Steele]

I never bother with the expression builder, so I don't know how to use it!

In a blank column in your query builder, simply type something like:

Differences: Work_Days([StartingDate], [EndingDate])

Replace StartingDate and EndingDate with whatever your fields are named.

 

[Guest]

This worked. Thanks. But it's returning a day count. How do I get the
hours so that I get 23 hours instead of 1 day?

I never bother with the expression builder, so I don't know how to use it!

In a blank column in your query builder, simply type something like:

Differences: Work_Days([StartingDate], [EndingDate])

Replace StartingDate and EndingDate with whatever your fields are named.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I named it DateFunction. My question is how do I use it in a query? When I
look up the function, I see the module name, but the function is not there.

 

[fnhc]

What did you name the module? If it's also Work_Days, rename it.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


name

 

[Douglas J. Steele]

You'd have to rewrite the Work_Days function to do that, and, assuming you
want to count working hours (say, only 9:00 to 5:00), it's actually a
non-trivial exercise to do.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

This worked. Thanks. But it's returning a day count. How do I get the
hours so that I get 23 hours instead of 1 day?

I never bother with the expression builder, so I don't know how to use it!

In a blank column in your query builder, simply type something like:

Differences: Work_Days([StartingDate], [EndingDate])

Replace StartingDate and EndingDate with whatever your fields are named.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I named it DateFunction. My question is how do I use it in a query?

When
I

look up the function, I see the module name, but the function is not there.

:

What did you name the module? If it's also Work_Days, rename it.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I can't get this to work. I'm a beginer-intermediate Access user

and
I've

gotten as far as creating the module with this code...What next?

When
I

use
the expresion builder to use the function, it's not there. I see

the
name

of
the module, but the function is not there. Please help.

Thanks.

Option Explicit

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