If...End If within a With... End With loop bombs if null fields are involved. See code below

[dwwally]

Sub New_Maturity_Rate_FWS_Test()

   On Error GoTo SubError
   Dim dbsRate As DAO.Database
   Dim tdfRate As DAO.TableDef
   Dim rstRate As Recordset
   Dim strDate As String
   Dim FieldCount As Integer
   Dim i As Integer
   Dim HighRate As Single
   Dim Rate36Mo As Single
   Dim Sum As Single
   Dim Count As Integer
   Dim Avg As Single

   Dim RateTable As String
   Dim AssessDate As Date
   Dim FinalRate As Single

   Rem this is a large table with interest rates for 80+ investment types from 1972 to the present
   Rem I am pulling only one date and only for three year investments for the code belowfor one date 71/1979
   RateTable = "Investment All Rates Combined"


   AssessDate = #7/1/1979#

   Set dbsRate = CurrentDb
   Set tdfRate = dbsRate.TableDefs(RateTable)
   Set rstRate = dbsRate.OpenRecordset("SELECT * FROM [" & RateTable & "] ORDER BY [Date]", dbOpenSnapshot)

   With rstRate
      FieldCount = .Fields.Count - 1
      For i = 0 To FieldCount
Debug.Print "REM ", i, .Fields(i).Name, .Fields(i).Value
      Next i
   End With

   Rem data only for 3-year investments produced by this loop, all rates are numbers
   Rem 0 Date 1/1/1979
   Rem 32 Treas Bond 3 Year_Rate 8.94
   Rem 35 3 Yr Fed Home Loan Yield Null
   Rem 37 3 Yr Fed Land Bank Yield Null
   Rem 39 3 Yr Fed Farm Credit Yield Null
   Rem 41 3 Yr Stud Loan Mrktg Yield Null
   Rem 43 3 Yr TVA Yield Null

   strDate = "Date = " & "#" & AssessDate & "#"   '"
   With rstRate
      .FindFirst strDate
      If .NoMatch Then
         MsgBox "Date " & strDate & " not found in rate table. See author."
         Exit Sub
      End If
   End With

   With rstRate
      HighRate = 0
      Rem this filed has a value of 14.72 and the If...End If works
      If Not IsNull(.Fields(32)) Then
         FinalRate = .Fields(32).Value
         Count = Count + 1
         Sum = Sum + FinalRate
         HighRate = FinalRate
      End If
      Rem all the If....End If below I had to block out because the fields 35, 37, 39, 41 and 43 have null values
      Rem unblocking these IF...End If creates an error
      Rem End With without With
      Rem the program debug then results in:
      Rem Rate36Month 7/1/1979 8.94 1 8.94 8.94

      ' If Not IsNull(.Fields(37)) Then
      ' FinalRate = .Fields(37)
      ' Sum = Sum + FinalRate
      ' HighRate = FinalRate
      ' If FinalRate > HighRate Then HighRate = FinalRate
      ' End
      ' If Not IsNull(.Fields(39)) Then
      ' FinalRate = .Fields(39)
      ' Sum = Sum + FinalRate
      ' HighRate = FinalRate
      ' If FinalRate > HighRate Then HighRate = FinalRate
      ' End If
      ' If Not IsNull(.Fields(41)) Then
      ' FinalRate = .Fields(41)
      ' Sum = Sum + FinalRate
      ' HighRate = FinalRate
      ' If FinalRate > HighRate Then HighRate = FinalRate
      ' End If
      ' If Not IsNull(.Fields(43)) Then
      ' FinalRate = .Fields(43)
      ' Sum = Sum + FinalRate
      ' HighRate = FinalRate
      ' If FinalRate > HighRate Then HighRate = FinalRate
      ' End If


      Rate36Mo = HighRate
      Avg = Sum / Count
Debug.Print "Rate36Month ", AssessDate, Rate36Mo, Count, Sum, Avg
      FinalRate = Rate36Mo
   End With

SubError_Exit:

   Exit Sub

SubError:
   MsgBox Err.Description
   MsgBox Err.Number
   Resume SubError_Exit


End Sub

[CJ_London]

your code is unreadable, please repost using indentation and code tags

[dwwally]

HOW DO i repost using indentation and code tags?

[CJ_London]

copy and paste your code, then highlight and click the code button (the one with # on the far right). If you use the advanced editor (click Go Advanced below), you can see what your post will look like

[John_G]

What is the error message you get?

If you comment out the On Error Goto Suberror line, you can find out which line is causing the error using the debug mode.

[pbaldy]

The error is because your If/End If are out of whack. Hint, End is not the same as End If.

[dwwally]

All of you, thanks for your help. I found the problem in my code. One of my
IF...End If I used by accident
If...End only. That caused the error.

[CJ_London]

if your isnull is not preventing the 'if' code from running, then the value is not null - perhaps it is a zero length text string?

[dwwally]

Dear pbaldy, I had found that error on my own. The reason I missed it because I had more than one hundred If...End Ifs
I missed it in 2 spots in my code, but the error debug did not stop on the "End" with the missing If
That would have made my life a lot easier. I am working with more than 60,000 lines of code in my program. Thanks

[pbaldy]

No problem, glad you found it.