If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
week day query
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 |
#2
|
|||
|
|||
week day query
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 "Joel Allen" wrote: 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 |
#3
|
|||
|
|||
week day query
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. "Joel Allen" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|