Grandkathy
Technical User
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