X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

3 Pages V   1 2 3 >  
Reply to this topicStart new topic
> Remove Weekends from query result?    
 
   
sblend63
post Apr 23 2004, 10:11 AM
Post #1

UtterAccess Addict
Posts: 258
From: Little Rock, AR



When subtracting =now() from a specified date, how do I return the result without the weekend days included?
Any help is appreciated.
Thanks,
Steve Blend
Go to the top of the page
 
+
tinygiant
post Apr 23 2004, 10:21 AM
Post #2

UtterAccess Ruler
Posts: 1,175
From: Nashville, GA



The easiest way would be to use the Excel function NETWORKDAYS or WORKDAYs, but it requires that Excel be installed on the computer(s) that are running the database.
therwise, unless it's already been done by someone, you'd have to identify the day (Saturday, Sunday, Holiday) and compare that against a list of days that should not be included in the result. The actual programming to accomplish it should not be very difficult. I'd be (and others here I'm sure) happy to help if you need it.
Go to the top of the page
 
+
xteam
post Apr 23 2004, 10:22 AM
Post #3

UtterAccess VIP
Posts: 3,667
From: Toronto, ON



Now() - [YourDate] - 2*Int((Now()-[YourDate])/7)
Go to the top of the page
 
+
xteam
post Apr 23 2004, 10:23 AM
Post #4

UtterAccess VIP
Posts: 3,667
From: Toronto, ON



i assumed you want to exclude only weekends rather than all statutory holidays...
Go to the top of the page
 
+
sblend63
post Apr 23 2004, 10:36 AM
Post #5

UtterAccess Addict
Posts: 258
From: Little Rock, AR



Holidays would be nice, but the main thing is the weekends..
Go to the top of the page
 
+
sblend63
post Apr 23 2004, 10:45 AM
Post #6

UtterAccess Addict
Posts: 258
From: Little Rock, AR



I tried what you have listed above as such and it returned "error" records:
ow() - [order date] - 2*Int((Now()-[order date])/7)
Go to the top of the page
 
+
xteam
post Apr 23 2004, 10:48 AM
Post #7

UtterAccess VIP
Posts: 3,667
From: Toronto, ON



oops, i gave you a wrong formula, actually is a little bit more complex, if Today is weekend ...or [YourDate] is weekend, my formula doesn't work
Go to the top of the page
 
+
sblend63
post Apr 23 2004, 10:51 AM
Post #8

UtterAccess Addict
Posts: 258
From: Little Rock, AR



I have Excel intalled on this PC. I tried the following:
etworkdays([order date],now(),holidays)
And it returned the message: Undefined function "Networkdays" in expression
Go to the top of the page
 
+
tinygiant
post Apr 23 2004, 10:57 AM
Post #9

UtterAccess Ruler
Posts: 1,175
From: Nashville, GA



You have to instantiate excel first to be able to use its funcitons. Try this:

CODE
Private Sub xlNetWorkDays()
nbsp;  Dim objExcel As Excel.Application
   Set objExcel = CreateObject("Excel.Application")
   MyVariable = objExcel.Application.NetWorkDays([OrderDate],now,holidays)
   objExcel.Quit
   Set objExcel = Nothing
End Sub


Haven't tried this on my computer yet, but I think it should work.
Go to the top of the page
 
+
sblend63
post Apr 23 2004, 11:08 AM
Post #10

UtterAccess Addict
Posts: 258
From: Little Rock, AR



OK,
There do I insert this code?
Thanks!
Go to the top of the page
 
+
bykram
post Apr 23 2004, 11:15 AM
Post #11

UtterAccess Addict
Posts: 149
From: Houston, TX



If your weeks are set up as Sun-Sat
then
Sunday: Format(Int(([Date]+6)/7)*7-6,"m/d/yy")
Saturday: Format(Int(([Date]+6)/7)*7+1,"m/d/yy")
o you would just have
..... & "WHERE [date]<> " & Sunday & " OR [date] <> " & Saturday
Go to the top of the page
 
+
tinygiant
post Apr 23 2004, 11:17 AM
Post #12

UtterAccess Ruler
Posts: 1,175
From: Nashville, GA



This is just another function you insert into the form's module (if private) or into a regular module if want it public. Then, you call the function from an AfterUpdate event or wherever else you want to call it from.

For example...

CODE
Private Sub MyDateControl_AfterUpdate()
nbsp;    Call xlNetWorkDays
End Sub

You can also modify the code to allow the input of date variables to make it more useful. More example, you can change the name of the sub from 'Private Sub xlNetWorkDays' to 'Private Function xlNetWorkDays(datOrderDate as Date) as Integer'.

Then call it with the code: WorkDays=xlNetWorkDays([OrderDate])

This would be the final product (function form):

CODE
Private Sub MyControl_AfterUpdate()
Dim intWorkDays as integer
     intWorkDays=xlNetWorkDays([OrderDate])
     (Other Programming Statements)
End Sub
Private Function xlNetWorkDays(datDate1 as Date) as Integer
   Dim objExcel As Excel.Application
   Set objExcel = CreateObject("Excel.Application")
   MyVariable = objExcel.Application.NetWorkDays(datDate1, Now())
   objExcel.Quit
   Set objExcel = Nothing
End Function


The great thing about using the Excel function is that you can modify the code to allow the removal of holidays because the excel function NetWorkDays allows it.
Go to the top of the page
 
+
ScottGem
post Apr 23 2004, 11:19 AM
Post #13

UtterAccess VIP / UA Clown
Posts: 27,961
From: LI, NY



Here's my version of a NetWrokdays function that takes Holidays into account:
ublic Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer
Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer
intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If WeekDay(dteCurrDate, vbMonday) < 6 Then
If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & dteCurrDate & "#")) Then
NetWorkdays = NetWorkdays + 1
End If
End If
Next i
End Function
To use this function with the Holidays feature you need to have a table named Holidays with the fields;
HolDate (PK)
Holiday (text description of holiday) Otherwise remove the lines in red.
This function goes in a global module.
Go to the top of the page
 
+
dallr
post Nov 12 2004, 05:58 AM
Post #14

UtterAccess VIP
Posts: 3,075
From: The Land of the Access Ninja's



I am now getting into the VBA side of access so the above code is a little difficult for me to figure out. I have some notes that i printed from the internet on programming in VBA which I am now starting to use but it will take me a while still to get the hang of things. It seems like filtering out weekdays and holidays is a common thing for person using Access there must be a simplier way of doing this. I myself need to remove holidays and sundays in a database that I am building. Can someone tell me how to do this from a query.
Go to the top of the page
 
+
ScottGem
post Nov 12 2004, 08:46 AM
Post #15

UtterAccess VIP / UA Clown
Posts: 27,961
From: LI, NY



There isn't a simpler way of doing it. My code is pretty straightforward and I would be happy to explain anything about it you don't understand. However, if you follow the instructions exactly, you don't need to really understand the code, just how to use the custom function.
It would also help to explain what you mean by "removing" holidays and sundays.
Go to the top of the page
 
+
dallr
post Nov 12 2004, 09:18 AM
Post #16

UtterAccess VIP
Posts: 3,075
From: The Land of the Access Ninja's



Let me say firstly thanks for your willingness to help it is much appreciated. My choice of words was poor. What I really ment to say is not include holidays and Sundays.
will go with your recommendation and try the code and get back to you.
PS: Thanks again
Go to the top of the page
 
+
ScottGem
post Nov 12 2004, 09:23 AM
Post #17

UtterAccess VIP / UA Clown
Posts: 27,961
From: LI, NY



I'm still not clear, include them in what? If you are trying to find the number of days between two dates exclusing holidays and Sundays, then my code will work except you need to change this line to:
If WeekDay(dteCurrDate, vbMonday) < 7 Then
thewise it will exclude Saturdays too.
Go to the top of the page
 
+
dallr
post Nov 12 2004, 11:08 AM
Post #18

UtterAccess VIP
Posts: 3,075
From: The Land of the Access Ninja's



Sorry if my explanation was vague. Your last comments summed it up. I have a date range and want to exclude sundays and holidays from it.
Go to the top of the page
 
+
ScottGem
post Nov 12 2004, 11:33 AM
Post #19

UtterAccess VIP / UA Clown
Posts: 27,961
From: LI, NY



Ok, just wanted to make sure. You understood the modification you need to make?
Go to the top of the page
 
+
logisticsman
post Mar 8 2007, 09:59 AM
Post #20

UtterAccess Member
Posts: 27
From: Connecticut



I've done some searching on this site and I've read this thread, however I can't seem to find quite what I'm looking for. The neat NetWrokdays function posted by Scott touches what might work for me.
Here's what I need this for:
I have a 'potential sales' report for the current month that I run every week. There is info from different categories that are displayed on the report that all roll into the final number. Some example of the categories are "in shipping" "in process" "allocated at warehouse" "in-stock but unallocated" etc. What I have to do when I run this report the last week of the month is manually go in and change all of the conditions on the queries. Our allocation and order data looks out three days, but in the conditions of my query it's limited to the current month by the following code: <=(Year(Date()) & IIf(Month(Date())<10,"0" & Month(Date()),Month(Date())) & IIf(Day(Date())<10,"0" & Day(Date()),Day(Date())))
So for this month, the last week I will change that to <=200704 and whatever day is three business days into April.
Any suggestions on how I could use the networkdays logic in this query?
thank you!
Go to the top of the page
 
+

3 Pages V   1 2 3 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 22nd December 2014 - 02:27 PM