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.

heres the code:



This will exclude Sundays and my Holidays - but how do I exclude Satrudays as well??

That code will exclude Saturdays as well.

That's funny - but when I call this code from my query by creating a calculated field that says:

dateReleased: 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
i = 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??

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.

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
i = i + 1
End If

Instead of the current = If Weekday(dteCurrDate, vbMonday) ???

????

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??

Any chance of you posting a stripped down version of your db with enough sample data to demonstrate the problem?

Ill give it a try... give me about 20 min

hopefully this is stripped enough but still enough info!

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.

Note: 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.

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??

rg,

I 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!!

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.