days between 2 dates w/o weekends

[2Blessed4Stress]

I am calculating the days between 2 dates in my qry using the DateDiff
function in my qry. How can I omit weekends from my day count?

 

[Wayne-I-M]

Hi

Create a new module and paste this into it

Function CountWODays(Date1 As Date, Date2 As Date) As Long
Dim DayCntr As Long, LoopDate As Date, WD As Long
DayCntr = 0
For LoopDate = Date1 To Date2
WD = Weekday(LoopDate)
If WD > 1 And WD < 7 Then '1-Sunday 7-Saturday
DayCntr = DayCntr + 1
End If
Next LoopDate
End Function


Save it and then in your query you can call it by putting something like in
the criteria section

WeekdaysCount: CountWODays([StartDate],[EndDate])



Hope this helps

 

[Douglas J. Steele]

Take a look at the Date/Time section of "The Access Web" for a couple of
possiblities http://www.mvps.org/access/datetime/ or take a look at my
September, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access".

You can download the column (and sample database) from
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Wayne-I-M]

Hi Douglas.

I just open you pdf regarding simulated checkoxes (windings font).

Can I just say I thought it was really good. Thanks for the tips. It is
one of the question I get asked quite often (can you make that box bigger) I
have always said no but from now .....

Thanks