Pushing dates in production schedule

[OCStan]

I added the function to a module. How do I run the function?

[RuralGuy]

It will be used in your Update query. Something like:
Set OldDateField = BumpDate(OldDateField, 1)

[OCStan]

Sorry but I'm not sure where to add this in the update query.

[RuralGuy]

Post the SQL for your Update query.

[OCStan]

Here is the SQL for the update query.

UPDATE [Job Info] INNER JOIN STRUCTURES ON [Job Info].[Order Number] = STRUCTURES.[ORDER NUMBER] SET STRUCTURES.SCHEDULED = DateAdd("d",1,[scheduled])
WHERE (((STRUCTURES.SCHEDULED) Is Null) AND ((STRUCTURES.PRIORITY)>0)) OR (((STRUCTURES.SCHEDULED)>=Date()));

[RuralGuy]

Change this: SET STRUCTURES.SCHEDULED = DateAdd("d",1,[scheduled])
...to... : SET STRUCTURES.SCHEDULED = BumpDate(scheduled, 1)

[OCStan]

That works great, thank you. Is it possible to add holidays as skip days?

[RuralGuy]

Yes, but I have to leave for several hours so you'll have to wait for my help on this. Sorry.

[RuralGuy]

Replace the existing Function with this code. Assuming a tblHolidays, this should skip those as well.

Function BumpDate(InDate As Date, BumpBy As Integer) As Date
' Adjust the InDate by the BumpBy value, skipping weekends and Holidays
' If you want to bump by one then use:
' NewDate = BumpDate(OldDate, 1)
   On Error GoTo Err_BumpDate
   Dim rst As DAO.Recordset
   Dim dbs As DAO.Database
   Dim Looping As Boolean
   On Error GoTo ErrHandler
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)
   Looping = True
   BumpDate = InDate
   Do While Looping
      BumpDate = BumpDate + BumpBy
      If Weekday(BumpDate, vbMonday) < 6 Then
         ' This is a weekday. Check for a Holiday
         rst.FindFirst "[HolidayDate] = #" & Format(BumpDate, "mm\/dd\/yyyy") & "#"
         If rst.NoMatch Then
            Looping = Weekday(BumpDate, vbMonday) > 5
         End If
      End If
   Loop
Exit_BumpDate:
   On Error Resume Next
   rst.Close
   Set rst = Nothing
   Set dbs = Nothing
   Exit Function
Err_BumpDate:
   MsgBox "Error No:    " & lngErrNumber & vbCrLf & _
          "Description: " & strErrDescription
   Resume Exit_BumpDate
End Function

[OCStan]

Thank you. This has been very helpful.