If Then Code

[RuralGuy]

I'm sorry, I'm spacing out here. Make the following red changes:

If Weekday([COLOR=Red]Me.WOSD[/COLOR], vbMonday) > 5 Then
  '-- It's the weekend - subtract 2 more days
  Me.WOSD = DateAdd("d", -2, WOSD)
Else
[COLOR=Red]'  MsgBox Me.WOSD & " is not on a weekend!"
  MsgBox "[" & Me.WOSD & "] has a Weekday of [" & Weekday(Me.WOSD, vbMonday) & "]"[/COLOR]
End If

 

[renenger]

A message box came up with 9/9/2005 has a weekday of 5.

 

[RuralGuy]

Light bulb goes off!! ScottGem pointed us to workdays and I was not paying attention. We need to subtract workdays not just days. You could incorporate the method he suggested which requires a HolidayTable or just modify the code to work without the HolidayTable.

Do you want to give it a try? I'll work on it too in case you need help.

 

[renenger]

Yes! Thanks I will give that a shot. I started a Holiday Table. I will mess with that code and see what I can come up with. I will probably need some help as I am still trying to learn this VB code!!!

 

[RuralGuy]

I'll be here. Have fun and good luck.

 

[renenger]

I'm getting a syntax error on this:

If Weekday(dteCurrDate, vbMonday) <= 5 AND IsNull(DLookup("[Holiday]","tblHolidays", [HolDate] = #" & dteCurrDate & "#") Then

 

[renenger]

I add a Holiday table called tblHolidays with HolDate and Holiday [text description].

 

[renenger]

I add a Holiday table called tblHolidays with HolDate and Holiday [text description].

 

[RuralGuy]

Try this:

If Weekday(dteCurrDate, vbMonday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & dteCurrDate & "#")) Then

 

[renenger]

Can I email you a copy of our DB. I have cleaned out all the information and put in some bogus info. I can't get my DB small enough to attach.

 

[RuralGuy]

Sure. Rural Guy at Wild Blue dot Net without the spaces will work.

 

[ScottGem]

I'm getting a syntax error on this:

If Weekday(dteCurrDate, vbMonday) <= 5 AND IsNull(DLookup("[Holiday]","tblHolidays", [HolDate] = #" & dteCurrDate & "#") Then

As Rural pointed out you are missing the closing parentheses. Try zipping your db before attaching.

 

[RuralGuy]

ScottGem, here's the code I ended up with:

Public Function MinusWorkdays(dteStart As Date, intNumDays As Integer) As Date
On Error GoTo Err_MinusWorkDays

MinusWorkdays = dteStart
Do While intNumDays > 0
   [b]MinusWorkdays = MinusWorkdays - 1[/b]
   If Weekday(MinusWorkdays, vbMonday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & MinusWorkdays & "#")) Then
      intNumDays = intNumDays - 1
   End If
Loop

Exit_MinusWorkDays:
Exit Function

Err_MinusWorkDays:
   MsgBox "Error No:    " & Err.Number & vbCr & _
          "Description: " & Err.Description
   Resume Exit_MinusWorkDays
   
End Function

Moving the date decrement above the WeekDay test solved the - we ended on a Sunday problem. Thanks for the initial start. I'll try to pay closer attention next time.

renenger sent me the stripped down mdb and I got several things to work and returned it. Thanks for the assistance.

 

[ScottGem]

My original function was written for adding days. I though I had account for the fact he was going backwards. Thanks for fixing that.

 

[renenger]

I want to thank you for all your guidance and help. You have no idea how much you have helped me!!! I have one last question.

I would also like the WODD (Work Order Due Date) to update forward based on the WOSD. I played around with the code you added to update the WOSD date. This works great, but the user may override the Build Date if we choose to build the cabinets sooner because of space available in the plant, so I can't base it on the LotDelDate. Same setup, move forward 6,5, or 4 work days.

Private Sub UpdateWODD()

Dim AddColor As Boolean
Dim intNumDays As Integer

If Forms!FRMDELIVERY.Form.Status = "In Mill" Or _
Forms!FRMDELIVERY.Form.Status = "Sander" Or _
Forms!FRMDELIVERY.Form.Status = "In UV" Or _
Forms!FRMDELIVERY.Form.Status = "In Bank" Or _
Forms!FRMDELIVERY.Form.Status = "In Paint" Or _
Forms!FRMDELIVERY.Form.Status = "On Floor" Or _
Forms!FRMDELIVERY.Form.Status = "In Assembly" Or _
Forms!FRMDELIVERY.Form.Status = "Completed" Then
' '-- The process has started, too late to alter the Due Date!
Exit Sub
Else

If Not (Forms!FRMDELIVERY.Form.SpecialColor = 0) Then
AddColor = True
Else
AddColor = False
End If

Select Case Me.Style

Case "Eagle", "H/E", "F/E", "RP-9", "RP-22", "RP-23"
If AddColor Then
intNumDays = 6
Else
intNumDays = 5
End If

Case Else
If AddColor Then
intNumDays = 6
Else
intNumDays = 4
End If
End Select

Me.WODD = AddWorkdays(Me.LotDelDate, intNumDays)
End If

End Sub

Public Function AddWorkdays(dteStart As Date, intnNumDays As Integer) As Date
On Error GoTo Err_AddWorkDays

AddWorkdays = dteStart
Do While intNumDays > 0
AddWorkdays = AddWorkdays + 1
If Weekday(AddWorkdays, vbMonday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & AddWorkdays & "#")) Then
intNumDays = intNumDays + 1
End If

Loop

Exit_AddWorkDays:
Exit Function

Err_AddWorkDays:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_AddWorkDays

End Function

 

[RuralGuy]

You have to count *down* the Number of days to add while adding the days:
intNumDays = intNumDays - 1