Filter out weekends in query?

[cedar72]

I am trying to creat a simple (I hope) query that will bring me the number of days between two dates and filter out weekend days. So if it was between Friday and Monday the count would be two.

here is the SQL as I have it now....

SELECT Neighborhood, Lot, Block, [Crit Dim Requested], [Actual Crit Dim], ([Actual Crit Dim]-[Crit Dim Requested]) AS [Diff Req/Actual]
FROM [Cabinet Installation Tracking Table]
WHERE ((([Crit Dim Requested])>=[Enter a Start date]));


Any help would be greatly appreciated!

 

[jjonesal]

You might want to do a keyword search in this forum for "weekend". I remember seeing a post for this same problem a while back.

J. Jones
jjonesal@cableone.net

 

[cmmrfrds]

Depending on your system settings, weekdays should be 2-6 with Sunday = 1 and Saturday = 7.

There are many ways, but you can try this.

SELECT Neighborhood, Lot, Block, [Crit Dim Requested], [Actual Crit Dim], ([Actual Crit Dim]-[Crit Dim Requested]) AS [Diff Req/Actual]
FROM [Cabinet Installation Tracking Table]
WHERE ((([Crit Dim Requested])>=[Enter a Start date])
AND Format(Weekday(YourDate), "d&quot IN (2,3,4,5,6));

 

[cedar72]

cmmrfrds....

Not sure if I follow...I need to count the weekdays between the two dates...not sure how your where clause is going to help

 

[cmmrfrds]

Sorry, I thought you just wanted to skip weekends in the query.

To skip weekends.
SELECT Count(Format(Weekday(YourDate), "d&quot) as daycnt
FROM [Cabinet Installation Tracking Table]
WHERE ((([Crit Dim Requested])>=[Enter a Start date])
AND Format(Weekday(YourDate), "d&quot IN (2,3,4,5,6));

You cannot mix aggregate and non-aggregate fields in a select so it will become a little more complex if you need additional fields.

 

[raskew]

Don't believe that the previous solutions are counting days.
Appears they are counting number of records. There have
been numerous examples in this forum of ways to count
weekdays, excluding weekends and, in some cases holidays.

Here's one you can plug into a module and call from your query.
Sample query:

SELECT [Enter a Start date] AS StartDate, [your date field], 
calcwkdays2([Enter a Start date],[your date field],False) AS NumDays
FROM [your table name]
WHERE ((([your date field])>=[Enter a Start date]));

Function CalcWkDays2(dteStartDate As Date, dteEndDate As Date, _
YCnt As Boolean, Optional pExcl As String = "17") As Integer
'*****************************************************
'Name:      CalcWkDays2 (Function)
'Purpose:   Count # of days between two dates, with
'           options to:
'           (1) Include or exclude the start date in count
'           (2) Specify weekdays to exclude (default
'               set to Saturday (7) & Sunday (1).  To exclude
'               Tuesday (3) & Thursday (5), specify "35".
'Parameters:    dteStartDate & dteEndDate formatted as dates
'               YCnt:   Specify True to include start date in
'                       count, False to exclude it.
'               pExcl:  Weekdays to exclude
'Inputs:    From debug window:
'           (1) ? CalcWkDays2(#01/01/01#, #07/01/01#, True)
'           (2) ? CalcWkDays2(#01/01/01#, #07/01/01#, False)
'           (3) ? CalcWkDays2(#01/01/01#, #07/01/01#, True, "")
'           (4) ? CalcWkDays2(#01/01/01#, #07/01/01#, False,"")
'           (5) ? CalcWkDays2(#07/01/01#, #01/01/01#, False,"")
'           (6) ? CalcWkDays2(#01/01/01#, #07/01/01#, False,"23456")
'Output:    (1) 130; (2) 129; (3) 182; (4) 181; (5) -181; (6) 52
'
'*****************************************************

Dim n As Integer, wdays As String, dateHold As Date, dteFlag As Boolean

dteFlag = False
'Reverse the dates if they were input backwards
If dteStartDate > dteEndDate Then
   dateHold = dteStartDate
   dteStartDate = dteEndDate
   dteEndDate = dateHold
   dteFlag = True
End If
n = 0
dteStartDate = dteStartDate - Not (YCnt)
'days to exclude (7 & 1 unless other specified)
wdays = pExcl

Do While dteStartDate <= dteEndDate
   n = n + IIf(InStr(wdays, WeekDay(dteStartDate)) = 0, 1, 0)
   dteStartDate = dteStartDate + 1
Loop
'return negative value if the Start Date
'was initially greater than the End Date
CalcWkDays2 = n * IIf(dteFlag, -1, 1)
End Function

 

[shannonp]

This problem has come up many times in various forums...you might want to check out the following website:

http://www.mvps.org/access/datetime/date0012.htm

 

[cedar72]

Thank you everyone! Raskew, your solution proved to be the best fit...thank you!