A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

week day query



 
 
Thread Tools Display Modes
  #1  
Old February 16th, 2007, 07:30 PM posted to microsoft.public.access.queries
Joel Allen
external usenet poster
 
Posts: 59
Default 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  
Old February 16th, 2007, 08:56 PM posted to microsoft.public.access.queries
geebee
external usenet poster
 
Posts: 87
Default 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  
Old February 16th, 2007, 09:07 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.