OfficeClosed Function /Holidays Module

[melanieh]

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.

 

[RuralGuy]

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)

 

[melanieh]

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.

 

[RuralGuy]

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)

 

[melanieh]

(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.

 

[RuralGuy]

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

 

[melanieh]

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

 

[RuralGuy]

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

 

[melanieh]

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.)

 

[RuralGuy]

Are InvoiceDate and OrderDate both DateTime fields?

 

[RuralGuy]

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.

 

[melanieh]

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.)

 

[RuralGuy]

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

 

[RuralGuy]

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

 

[melanieh]

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

 

[melanieh]

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

 

[RuralGuy]

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

 

[melanieh]

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.

 

[RuralGuy]

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.

 

[RuralGuy]

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.