locked
VBA nesting problem RRS feed

  • Question

  • Hi all,

    I am writing a VBA routing in Access 2010 to create a forward looking shift log. In one section of the code, I am calculating the dates for the trial and final payroll balance reports. The dates are based on known mid month and end month dates in a table as well as federal and company holidays. The problem I am having is a compile error "End If without block If" on the End If just prior to the rst.MoveNext command. I don't know if I have a missing End If statement or if the error is related to nesting limits in VBA. I have searched the web, but have not found what the nesting limit is for VBA in Access 2010.

    The code is below. Any insights?

            'Add one-off job for MCW payroll, if any
               Set rst = dbs.OpenRecordset("tblMCWPayDates", dbOpenDynaset)
               Do While Not rst.EOF
                  intPayDays = DateDiff("d", dtGetDate, rst![MCWPayDate])
                  If intPayDays <= 11 Then
                     strPayDay = WeekdayName(Weekday(DatePart("w", rst![MCWPayDate])))
                     If DatePart("d", rst![MCWPayDate]) >= 14 And _
                        DatePart("d", rst![MCWPayDate]) <= 16 Then
                        strPayPeriod = "Mid"
                     Else
                        strPayPeriod = "End"
                     End If
                  Select Case [strPayPeriod]
                     Case "Mid"
                        Select Case [strCurrentMonth]
                           Case "October"
                              Select Case [strPayDay]
                                 Case "Wednesday"
                                    strDayType = Switch([intPayDays] = 6, "MCW Final 1", _
                                                        [intPayDays] = 7, "MCW Trial 2", _
                                                        [intPayDays] = 8, "MCW Trial 1")
                                    Exit Do
                              End Select  'strPayDay
                           Case "November"
                              Select Case [strPayDay]
                                 Case "Wednesday"
                                    strDayType = Switch([intPayDays] = 6, "MCW Final 1", _
                                                        [intPayDays] = 7, "MCW Trial 2", _
                                                        [intPayDays] = 8, "MCW Trial 1")
                                    Exit Do
                              End Select  'strPayDay
                        End Select 'strCurrentMonth
                        Select Case [strPayDay]
                           Case "Wednesday"
                              strDayType = Switch([intPayDays] = 5, "MCW Final 1", _
                                                  [intPayDays] = 6, "MCW Trial 2", _
                                                  [intPayDays] = 7, "MCW Trial 1")
                              Exit Do
                           Case "Thursday"
                              strDayType = Switch([intPayDays] = 3, "MCW Final 1", _
                                                  [intPayDays] = 6, "MCW Trial 2", _
                                                  [intPayDays] = 7, "MCW Trial 1")
                              Exit Do
                           Case "Friday"
                              strDayType = Switch([intPayDays] = 3, "MCW Final 1", _
                                                  [intPayDays] = 4, "MCW Trial 2", _
                                                  [intPayDays] = 7, "MCW Trial 1")
                              Exit Do
                        End Select  'strPayDay
                           strDayType = Switch([intPayDays] = 5, "MCW Final 1", _
                                               [intPayDays] = 6, "MCW Trial 2", _
                                               [intPayDays] = 7, "MCW Trial 1")
                           Exit Do
                     Case "End"
                        Select Case [strCurrentMonth]
                           Case "May"
                              Select Case [strPayDay]
                                 Case "Monday"
                                    strDayType = Switch([intPayDays] = 5, "MCW Final 2", _
                                                        [intPayDays] = 6, "MCW Trial 4", _
                                                        [intPayDays] = 10, "MCW Trial 3")
                                    Exit Do
                                 Case "Tuesday"
                                    strDayType = Switch([intPayDays] = 5, "MCW Final 2", _
                                                        [intPayDays] = 6, "MCW Trial 4", _
                                                        [intPayDays] = 7, "MCW Trial 3")
                                    Exit Do
                                 Case "Wednesday"
                                    strDayType = Switch([intPayDays] = 6, "MCW Final 2", _
                                                        [intPayDays] = 7, "MCW Trial 4", _
                                                        [intPayDays] = 8, "MCW Trial 3")
                                    Exit Do
                                 Case "Friday"
                                    strDayType = Switch([intPayDays] = 3, "MCW Final 2", _
                                                        [intPayDays] = 7, "MCW Trial 4", _
                                                        [intPayDays] = 8, "MCW Trial 3")
                                    Exit Do
                           Case "November"
                              Select Case [strPayDay]
                                 Case "Monday"
                                    strDayType = Switch([intPayDays] = 7, "MCW Final 2", _
                                                        [intPayDays] = 10, "MCW Trial 4", _
                                                        [intPayDays] = 11, "MCW Trial 3")
                                    Exit Do
                                 Case "Tuesday"
                                    strDayType = Switch([intPayDays] = 6, "MCW Final 2", _
                                                        [intPayDays] = 8, "MCW Trial 4", _
                                                        [intPayDays] = 11, "MCW Trial 3")
                                    Exit Do
                                 Case "Wednesday"
                                    strDayType = Switch([intPayDays] = 6, "MCW Final 2", _
                                                        [intPayDays] = 7, "MCW Trial 4", _
                                                        [intPayDays] = 8, "MCW Trial 3")
                                    Exit Do
                                 Case "Thursday"
                                    strDayType = Switch([intPayDays] = 6, "MCW Final 2", _
                                                        [intPayDays] = 7, "MCW Trial 4", _
                                                        [intPayDays] = 8, "MCW Trial 3")
                                    Exit Do
                                 Case "Friday"
                                    If DatePart("d", rst![MCWPayDate]) = 29 Then
                                       strDayType = Switch([intPayDays] = 4, "MCW Final 2", _
                                                           [intPayDays] = 7, "MCW Trial 4", _
                                                           [intPayDays] = 8, "MCW Trial 3")
                                       Exit Do
                                    Else
                                       strDayType = Switch([intPayDays] = 3, "MCW Final 2", _
                                                           [intPayDays] = 4, "MCW Trial 4", _
                                                           [intPayDays] = 9, "MCW Trial 3")
                                       Exit Do
                                    End If
                              End Select  'strPayDay
                           Case "December"
                              Select Case [strPayDay]
                                 Case "Tuesday"
                                    strDayType = Switch([intPayDays] = 6, "MCW Final 2", _
                                                        [intPayDays] = 7, "MCW Trial 4", _
                                                        [intPayDays] = 8, "MCW Trial 3")
                                    Exit Do
                                 Case "Wednesday"
                                    If DatePart("d", rst![MCWPayDate]) = 2 Then
                                       strDayType = Switch([intPayDays] = 6, "MCW Final 2", _
                                                           [intPayDays] = 7, "MCW Trial 4", _
                                                           [intPayDays] = 12, "MCW Trial 3")
                                       Exit Do
                                    Else
                                       strDayType = Switch([intPayDays] = 5, "MCW Final 2", _
                                                           [intPayDays] = 8, "MCW Trial 4", _
                                                           [intPayDays] = 9, "MCW Trial 3")
                                       Exit Do
                                    End If
                                 Case "Thursday"
                                    strDayType = Switch([intPayDays] = 6, "MCW Final 2", _
                                                        [intPayDays] = 7, "MCW Trial 4", _
                                                        [intPayDays] = 10, "MCW Trial 3")
                                    Exit Do
                                 Case "Friday"
                                    If DatePart("d", rst![MCWPayDate]) = 30 Then
                                       strDayType = Switch([intPayDays] = 3, "MCW Final 2", _
                                                           [intPayDays] = 8, "MCW Trial 4", _
                                                           [intPayDays] = 9, "MCW Trial 3")
                                       Exit Do
                                    Else
                                       strDayType = Switch([intPayDays] = 3, "MCW Final 2", _
                                                           [intPayDays] = 4, "MCW Trial 4", _
                                                           [intPayDays] = 9, "MCW Trial 3")
                                       Exit Do
                                    End If
                              End Select  'strPayDays
                        End Select  'strCurentMonth
                        Select Case [strPayDay]
                           Case "Wednesday"
                              strDayType = Switch([intPayDays] = 5, "MCW Final 2", _
                                                  [intPayDays] = 6, "MCW Trial 4", _
                                                  [intPayDays] = 7, "MCW Trial 3")
                              Exit Do
                           Case "Thursday"
                              strDayType = Switch([intPayDays] = 3, "MCW Final 2", _
                                                  [intPayDays] = 6, "MCW Trial 4", _
                                                  [intPayDays] = 7, "MCW Trial 3")
                              Exit Do
                           Case "Friday"
                              strDayType = Switch([intPayDays] = 3, "MCW Final 2", _
                                                  [intPayDays] = 4, "MCW Trial 4", _
                                                  [intPayDays] = 7, "MCW Trial 3")
                              Exit Do
                        End Select  'strPayDay
                        strDayType = Switch([intPayDays] = 6, "MCW Final 2", _
                                            [intPayDays] = 7, "MCW Trial 4", _
                                            [intPayDays] = 8, "MCW Trial 3")
                        Exit Do
                  End Select  'strPayPeriod
                  End If
                  rst.MoveNext
               Loop
               rst.Close


    • Edited by TJ_Gunn58 Wednesday, February 6, 2013 10:17 PM
    Wednesday, February 6, 2013 9:54 PM

Answers

  • I think it's actually the missing End Select within the Case May statement that is triggering the error. The compiler doesn't realize it's not still in a Select statement.
    • Marked as answer by TJ_Gunn58 Wednesday, February 6, 2013 10:23 PM
    Wednesday, February 6, 2013 10:18 PM

All replies

  • I think it's actually the missing End Select within the Case May statement that is triggering the error. The compiler doesn't realize it's not still in a Select statement.
    • Marked as answer by TJ_Gunn58 Wednesday, February 6, 2013 10:23 PM
    Wednesday, February 6, 2013 10:18 PM
  • Thanks cchimi. I missed that one.
    Wednesday, February 6, 2013 10:24 PM