Full Version: exclude Saturday as well as Sunday?
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
samohtwerdna
A while back I asked about excluding Sundays from queries and got several responses. This code I wanted to try to use because I could easily create a table with all my holiday dates to be excluded as well instead of writing and array.
eres the code:
CODE
Dim dteCurrDate As Date
Dim i As Integer
intNumDays = Int(curBudget/curDailyPay)
dteCurrDate = dteStart
AddWorkdays = dteStart
O= 1
Do While i < intNumDays
     If Weekday(dteCurrDate, vbMonday) <= 5 AND IsNull(DLookup("[Holiday]","tblHolidays", [HolDate] = #" & dteCurrDate & "#") Then
          i = i + 1
     End If
     dteCurrDate = dteCurrDate + 1
Loop
AddWorkdays = dteCurrDate
Exit_AddWorkDays:

This will exclude Sundays and my Holidays - but how do I exclude Satrudays as well??
RuralGuy
That code will exclude Saturdays as well.
samohtwerdna
That's funny - but when I call this code from my query by creating a calculated field that says:
ateReleased: AddWorkdays([dateSigned], 2) - I still Get Saturdays??
when I change the function to:
Public Function AddWorkdays(dteStart As Date, intNumDays As Integer) As Date
Dim dteCurrDate As Date
Dim i As Integer
'intNumDays = Int()
dteCurrDate = dteStart
AddWorkdays = dteStart
O= 1
Do While i < intNumDays
If Weekday(dteCurrDate, vbMonday) < 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HoliDate]" = "#" & dteCurrDate & "#")) Then
i = i + 1
End If
dteCurrDate = dteCurrDate + 1
Loop
AddWorkdays = dteCurrDate
Exit_AddWorkDays:
End Function
I don't seem to get Saturday's but I do seem to get the holidays??
RuralGuy
I would expect the 2nd code to skip Fridays as well. Key the following into your immediate window:
?Weekday(#01/06/2006#,vbMonday)
It should return 5 since it is a Friday.
samohtwerdna
Ok so I set up a little form with a feild called dateSigned then a calculated feild called ESD and a command button called cmdPush
When the dateSigned is entered I have an event procedure that calculates the date for the ESD like:
[ESD].Value = AddWorkdays([dateSigned].Value, 9)
then the cmdPush has this pocedure to push the ESD out by one day:
[ESD].Value = AddWorkdays([Forms]![frmMain]![ESD], 2)
With my AddWorkdays function exactly like the first post - If I scroll through dates and say my dateSigned is #5/15/2006# my ESD should be #5/30/2006# (skipping the memorial day holiday) instead I get #5/26/2006#
When I click the cmdPush the ESD changes to #5/27/2006# which is a Saturday - If I click cmdPush again I get #5/30/2006# then 5/31 then 6/1 then 6/2 then 6/3 (another Satruday) then 6/6 which is a Tuesday
Maybe the AddWordays function should be :
Do While i < intNumDays
If Weekday(dteCurrDate, vbSunday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HoliDate]" = "#" & dteCurrDate & "#")) Then
O= i + 1
End If
Instead of the current = If Weekday(dteCurrDate, vbMonday) ???
????
samohtwerdna
For the sake of experimentation I changed the code to vbSunday instead of vbMonday - now when I use the cmdPush I do skip all Saturday's and Sunday's but I don't seem to be skipping any holidays
I'm not sure that the IsNull is the correct way to go here??
Can someone explain??
RuralGuy
Any chance of you posting a stripped down version of your db with enough sample data to demonstrate the problem?
samohtwerdna
Ill give it a try... give me about 20 min
samohtwerdna
hopefully this is stripped enough but still enough info!
RuralGuy
As much as I like the code from Ken Getz and Mike Gilbert, I couldn't follow it very well so I added a routine on my own. See if this form works as you might expect.
ote: When the Access wizard places a control on a form, it defaults the name of the control to the same name as the field to which it is bound. This confuses Access when you start to write code. Therefore, you will see that I changed the names of the controls so Access and I knew what I was referring to in the code.
samohtwerdna
rg thanks a lot for your help and the well commented code. I think the code from Ken Getz and Mike Gilbert would work fine for the weekends but the holidays were just not working - and even if they where I prefer the holidays to be stored in a table that can easily be updated.
There is only one glitch - hopefully I can figure it out - but do you have any idea why when you change the cboCode the ESD value is not updated to match the CESD value??
samohtwerdna
rg,
found away to get the cboCode to update both ESD and CESD:
by changing
Me.ESD = Me.CESD
to
Me.txtESD = Me.CESD.Value
Hopefully there is no problem doing it this way, Please let me know if this is a major fopaw
Anyway, Thanks again for your help and time!! Very appreciated!!
RuralGuy
I had changed the control names after I wrote most of the code and started having problems. Looking at it again, I noticed there were several places in the code where I forget to change Me.ESD to Me.txtESD. What you did was correct but you might want to search for .ESD and change it to .txtESD in my code. I wanted to reference the controls and not the fields in all of the code I wrote. You might also like to know that in VBA it is not necessary to specify the .Value property, since that is the default property.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.