I have created the tables and module following the instructions on microsoft's page.
http://support.microsoft.com/kb/210064/en-us

The Module is:
Function OfficeClosed(TheDate) As Integer

OfficeClosed = False
TheDate = Format(TheDate, "dd/mm/yyyy")
' Test for Saturday or Sunday.
If WeekDay(TheDate) = 1 Or WeekDay(TheDate) = 7 Then
OfficeClosed = True
' Test for Holiday.
ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
& TheDate & "#")) Then
OfficeClosed = True
End If

End Function

I tested it in the immediate window in VB and it works ok.

-----------------------

Now..... for usage on my form:
I have an OrderDate field that inputs today's date automatically as the default value. I used =Date()

Then I have an InvoiceDate field that needs to add 2 days to the OrderDate but also avoid Saturday, Sunday and Holidays listed on the Holidays table.

______________________

The function listed on MS is ........
DueDate=OrderDate+30
Do While OfficeClosed(DueDate)
DueDate=DateDue+1
Loop

Where do I put that on my form? Within the field properties on invoice date and where - an event, the control source, the default? Do I replace the word duedate with something else or leave as is?

I'm confused as to where to put it. I've tried numerous ways to do this and am stumped as it is new to me to work with the module.

Thanks.

Just go ahead and use this different function instead.
Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
' If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[HoliDate]", "tblHolidays", _
"[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
'-- The above Format of PlusWorkdays works with US or UK dates!
intNumDays = intNumDays - 1
End If
Loop

End Function

Then put this in the AfterUpdate event of the OrderDate control.
Me.InvoiceDate = PlusWorkdays(Me.OrderDate, 2)

Thanks a bunch. I appreciate the help.

Can you tell me what I'm going incorrectly?
I put the code above into a module and named it PlusWorkdays.
Then I put the AfterUpdate event on the OrderDate control.

Nothing happens....... do I need to put the code somewhere else?
Thanks.

Does the code work from the immediate window? The AfterUpdate event of the OrderDate only runs when you change the OrderDate. (Don't be offended, just covering all of the bases)

(Thanks. I won't be offended at all as this is all completely new to me with the modules and all.)

I typed in the immediate window for the PlusWorkdays module: (hopefully this is correct.)
? PlusWorkdays (12/11/2007)
and it gives me:
Compile error: expected variable or procedure, not module.

? PlusWorkdays(#12/11/2007#,2)

same error
Compile error: expected variable or procedure, not module.

No space after PlusWorkdays right? Did you cut and paste from my post? What is the name of the module?

Yes, I did copy/paste it. (I checked it again to make sure no space too.)
The name of the module I have saved is PlusWorkdays.
I copied and pasted the code into that. (didn't change anything.)

Are InvoiceDate and OrderDate both DateTime fields?

Yes, I did copy/paste it. (I checked it again to make sure no space too.)
The name of the module I have saved is PlusWorkdays.
I copied and pasted the code into that. (didn't change anything.)Bingo!! Rename the module basPlusWorkdays. The function and the module can not be the same name.

That helped!
I have holidays listed in my holiday table.... tblHolidays
12/25/2007 (is in the table)

It works stupendously adding the additional days for the Saturday or Sunday date! woo-hoo!!! but it's not picking up the info. in the holiday table.
Any suggestions?
Thanks!
(I did paste all of the code into the module, don't know if I need to edit it somehow to follow the holidays.)

Can you single step the code to see if the DLookup() is working? Click outside the margin on the left to set a breakpoint.

Did I get the names of the table and field correct in the code?

I clicked and added a breakpoint.....
It selected all of this (is that correct?)
If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[HoliDate]", "tblHolidays", _
"[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then

I tried the form again and when it goes to the code for the error it points to:
"[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then

Yes! Now run it from the immediate window with (#12/24/2007#,2) and see what happens.

My table was named Holidays but I just made a quick copy of it after reading your code and name it tblHolidays to match it.
My other fields on the tblHolidays are HoliDate and HolidayName.
HoliDate is a date/time field but I don't have any formatting set on it. Didn't know if that could be it.

Better yet, (#12/21/2007#,2) is a longer test. F8 will single step the code and if you hover the cursor over a variable it will display the value contained in the variable.

My table was named Holidays but I just made a quick copy of it after reading your code and name it tblHolidays to match it.
My other fields on the tblHolidays are HoliDate and HolidayName.
HoliDate is a date/time field but I don't have any formatting set on it. Didn't know if that could be it.The change you made should allow the code to function correctly.

Breakpoint is still on there.
I typed in the immediate window: ? Plusworkdays(#12/24/2007#,2)

And it highlights the same lines again where it has the breakpoint.
If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[HoliDate]", "tblHolidays", _
"[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then

I typed in the immediate window: ? Plusworkdays(#12/21/2007#,2)12/26/2007
then hit the F8, it just takes me line by line through the code and then again highlights the same three lines above together.

I had changed the table name before having these things happen so it's still not picking things up. It seems to go over that last line. is dd supposed to have the / \ surrounding it?
(I know little about this so just curious.)
Thanks!

The:
If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[HoliDate]", "tblHolidays", _
"[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
...is really all on one line since we have the "_" continuation character at the end of the first two lines. Each time you press the F8 key the highlighting should move to the next line to execute. It should only highlight the intNumDays = intNumDays - 1 line twice and skip over it 3 times.

I've uploaded it as a zip file.
tblHolidays is in there.
Form2 is the form where I'm putting the info. (A customer ID of 1 can be put in then order date, then invoice date.No attachment Mel. Try it again.

It seems to be working Melanie.

well dang..... sorry about that
I did run in the immediate window:
? Plusworkdays(#12/24/2007#,2)

it correctly gives me 12/27/07.

But when I open my form: and type in 12/24/07 on the orderdate, when I tab to invoicedate it goes to the vba code and highlights those 3 lines again.

I'll go attach some screenshots.

Remove the breakpoint. Just click outside the margin again to remove it.

Bless you and thank you for sticking with me and helping me figure this out.

you might want to pull that back off. Unless its bogus info, your accounts table looks to have some personally identifiable information.

Bless you and thank you for sticking with me and helping me figure this out.You are certainly welcome Melanie. Everyone has to start somewhere. The more you play with VBA the better you will get and the more control you will have over your applications.

I'm sure making a mess of things today....
I deleted my message but the attachment is still in one of your replies to me.
How do I get rid of it?
Thanks.

RG's gonna have to do that.

don't worry about it, healthy levels of paranoia takes years to hone. plus we're a pretty honorable bunch.

Hi Melanie,
I deleted the attachment I had. All is now OK.