week day query

[Joel Allen]

Hi,

I'm trying to do a simple calculation of the number of weekdays between two
dates. This what I have in my query, but it's not working. Seems so
simple, but it's not. Anybody have any suggestions? Sorry, kind of slow
with this stuff!

Test: Weekday([Start Date]-[End Date])

Thanks, Joel

 

[geebee]

hi,

Put this code in a module:

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

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
find_weekdays = Work_Days

End Function

And here is a sample query calling the function in the module:
SELECT find_weekdays(#2/10/2007#,Date())-1 AS weekdaycount;

Hope this helps,
geebee

 

[KARL DEWEY]

Create a table named CountNumber with a number field named CountNUM
containing number zero through your maximum date spread.

I use a table named Joel_Allen and field named Sale to extract data. Use
the two queries below. The first list all dates between start and end. The
second select only weekdays and counts them. You could add a holiday table
and use it for additional criteria.

SELECT Joel_Allen.Sale, Joel_Allen.[Start Date], Joel_Allen.[End Date],
DateAdd("d",[CountNUM],[Start Date]) AS [Days Between]
FROM CountNumber, Joel_Allen
WHERE (((DateAdd("d",[CountNUM],[Start Date]))<=[End Date]))
ORDER BY Joel_Allen.Sale, DateAdd("d",[CountNUM],[Start Date]);


SELECT [Dates-Weekdays].Sale, [Dates-Weekdays].[Start Date],
[Dates-Weekdays].[End Date], Count([Dates-Weekdays].[Days Between]) AS
[CountOfDays Between]
FROM [Dates-Weekdays]
WHERE (((Weekday([Days Between])) Between 2 And 6))
GROUP BY [Dates-Weekdays].Sale, [Dates-Weekdays].[Start Date],
[Dates-Weekdays].[End Date];

You could add a holiday table and use it for additional criteria.