Finding Out if a date is a Holiday/Weekend

[Grandkathy]

I have a database that is used for reserving videos from our Safety & Health Video Library.

This db after a video id is entered, checks for the next available date it could be sent out.

After that, it checks to see if the shipping date is a weekend or a holiday. if it is, it changes the shipping date by adding one/two if it's Sunday or a Monday Holiday, or subtracting one/two if it's a friday holiday or saturday. Or whatever other data a holiday might be on, it adds a day to the shipping date.

my code isn't working for some reason.............................

<code

Public Function CheckHolidays()
'MsgBox "Called CheckHolidays"
DelivTyp = Forms!frmreservations.DeliveryTypeHold
rstDaysAllowed = Forms!frmreservations.DaysAllowedHold
rstDaysAllowed = CInt(rstDaysAllowed)

strSQL2 = "HolidaysTemp" 'temp table as run from qAvailVideo_MakeTable
Set cnn2 = CurrentProject.Connection 'sets the connection to current database
Set rst2 = New ADODB.Recordset 'sets the recordset to a new ADODB recordset
rst2.Open strSQL2, cnn2, adOpenStatic, adLockOptimistic, adCmdTable

If ShipHold = "" Or IsNull(ShipHold) Then
ShipHold = DateAdd("d", 1, date)
End If

While Not rst2.EOF
If ShipHold = rst2.Fields.Item("HolidayDate") Then
ShipHold = DateAdd("d", 1, ShipHold)
End If
rst2.MoveNext
Wend

rst2.MoveFirst

If DelivTyp = 2 Then '2 = shipping (needs 7 days to ship) & 1 = Pick-up (only needs 2 days)
ShowHold = DateAdd("d", 7, ShipHold)
Else
ShowHold = DateAdd("d", 2, ShipHold)
End If
End Function

<Code

I've tried changing this in many ways, but I think I'm just loosing my mind on this one. Here's one of the ways I've changed things.

<Code
Public Function CheckHolidays()

DelivTyp = Forms!frmreservations.DeliveryTypeHold
rstDaysAllowed = Forms!frmreservations.DaysAllowedHold
rstDaysAllowed = CInt(rstDaysAllowed)

strSQL2 = "HolidaysTemp" 'temp table as run from query 'MakeHolidayTemp'
Set cnn2 = CurrentProject.Connection 'sets the connection to current database
Set rst2 = New ADODB.Recordset 'sets the recordset to a new ADODB recordset
rst2.Open strSQL2, cnn2, adOpenStatic, adLockOptimistic, adCmdTable 'opens the recordset MAKE SURE ALL PARAMETERS ARE INCLUDED - THIS WILL NOT WORK WITHOUT THEM!!!

If ShipHold = "" Or IsNull(ShipHold) Then 'if 1
ShipHold = DateAdd("d", 1, date)
End If ' if 1

While Not rst2.EOF
If ShipHold < rst2.Fields.Item("HolidayDate") Then 'if 2
Exit Function
Else ' if 2
If DelivTyp = 1 Then 'if 4 *****1;PickUp
If Weekday(ShipHold) = 1 Then 'if '5 **** weekday 1 = Sunday
Forms!frmreservations.ShipDate = DateAdd("d", -2, ShipHold)
Forms!frmreservations.ShowDate = DateAdd("d", 3, ShipHold)
Forms!frmreservations.ShipBackDate = DateAdd("d", 10, ShipHold)
Forms!frmreservations.DueDate = DateAdd("d", 17, ShipHold)
Exit Function
ElseIf Weekday(ShipHold) = 7 Then 'if 5 ***** weekday 7 = Saturday
Forms!frmreservations.ShipDate = DateAdd("d", -1, ShipHold)
Forms!frmreservations.ShowDate = DateAdd("d", 7, ShipHold)
Forms!frmreservations.ShipBackDate = DateAdd("d", 14, ShipHold)
Forms!frmreservations.DueDate = DateAdd("d", 21, ShipHold)
Exit Function
End If 'if 5
Else 'if 4
Forms!frmreservations.ShipDate = ShipHold
Forms!frmreservations.ShowDate = ShipHold + 7
Forms!frmreservations.ShipBackDate = ShipHold + 14
Forms!frmreservations.DueDate = ShipHold + 21
Exit Function
End If 'if 4
rst2.MoveNext
End If 'if 2
Wend

rst2.MoveFirst

This table 'HolidaysTemp' is run from a query (daily), which only puts weekends and holidays > date(). So, if the shipdate is < holidaydate, then I can get out. Otherwise, I need it to run through the table until the shipdate is < holidate.

Hopefully this is making sense.

Kathy

 

[hkaing79]

What part of your code isn't working?

 

[Grandkathy]

Sorry about that.

If Weekday(ShipHold) = 1 Then 'if '5 **** weekday 1 = Sunday
Forms!frmreservations.ShipDate = DateAdd("d", -2, ShipHold)
Forms!frmreservations.ShowDate = DateAdd("d", 3, ShipHold)
Forms!frmreservations.ShipBackDate = DateAdd("d", 10, ShipHold)
Forms!frmreservations.DueDate = DateAdd("d", 17, ShipHold)
Exit Function
ElseIf Weekday(ShipHold) = 7 Then 'if 5 ***** weekday 7 = Saturday
Forms!frmreservations.ShipDate = DateAdd("d", -1, ShipHold)
Forms!frmreservations.ShowDate = DateAdd("d", 7, ShipHold)
Forms!frmreservations.ShipBackDate = DateAdd("d", 14, ShipHold)
Forms!frmreservations.DueDate = DateAdd("d", 21, ShipHold)
Exit Function
End If 'if 5
Else 'if 4
Forms!frmreservations.ShipDate = ShipHold
Forms!frmreservations.ShowDate = ShipHold + 7
Forms!frmreservations.ShipBackDate = ShipHold + 14
Forms!frmreservations.DueDate = ShipHold + 21

This piece isn't adding or subtracting any dates

 

[hkaing79]

Is the form bound to the table? If it's unbound, you should see the dates change on the form but not in the table.

I created a sample form and used DateAdd in the same format you did and it worked for bound form.

Try adding msgbox to debug your program. For example...

If Weekday(ShipHold) = 1 Then
msgbox("Weekday(ShipHold)=1")
Forms!frmreservations.ShipDate = DateAdd("d", -2, ShipHold)
...

And make sure that your conditions are being satisfied.

I assume you meant to include DateAdd for the last four Date additions?

 

[Grandkathy]

Yes, I do intend to add DateAdd to the last for date additions.

I've tried some msgboxes, but evidently not in the correct place.

Thanks, I'll try putting more message boxes in and see what I get.

 

[hkaing79]

Out of curiosity, what are you using the rst2 for? And what data do you have in HolidaysTemp? Is ShipHold a local variable, control in a form, field in a table?

 

[Grandkathy]

HolidaysTemp holds 2 fields:
HolidayDate HolidayName
3/19/2005 Weekend
3/20/2005 Weekend
.
.
9/5/2005 Labor Day

ShipHold is a Global Variable

rst2 is for this function the recordset

Set rst2 = New ADODB.Recordset 'sets the recordset to a new ADODB recordset

 

[hkaing79]

Yea, I'm trying to figure out why you're using rst of the holidays.

Also, for your HolidaysTemp, you just need to enter the holidays because the Weekend function takes care of weekend. So technically, all you need is a one field table with Holiday Date.

Instead of looping through the table, you can use DLookup.

If IsNull(DLookup("HolidayDate","HolidaysTemp","HolidayDate=#" & ShipHold & "#") Then 'This means the ShipHold date is not a holiday because it's not in the table

 

[MichaelRed]

you might look at faq181-261 it is not intended for your specific purpose, but it does show alternatives to the processes you are using for both the holidays and weekend days in 'schedualing' date info.

there are alos numerous other faqs and threads dealing wiuth thie generic topic. use of the search capability in these fora with reasonable key words should turn up several or more of interest.





MichaelRed