Run-Time error 2001

[mhprol]

Heya.

I'm having a problem with a specific Report Routine in Access 2003. I've browsed the forums for advice but I'm still not able to identify why I'm getting this error.

Here is the Sub code:

Dim rs As Recordset, month, rank, control, partner, strSQL
    'Retrieve current month's top 5 Point Partners
    'this recordset retrieves two fields, (0) Partner Name and (1) Total Points
    Set rs = CurrentDb.OpenRecordset("qryTop5")
    rs.MoveFirst
    'If query operation was succesful, format report controls
    If Not rs.EOF Then
        'Retrieve Total Points for last month's Top5 partners for previous 12 months
        For month = 1 To 12 Step 1
            For rank = 1 To 5 Step 1
                'Define which label's caption should be modified according to Month and Rank
                If month > 9 Then
                    control = "lblParceirosP" & rank & month
                Else
                    control = "lblParceirosP" & rank & "0" & month
                End If
                'Query only includes Top5 partners' points for last month
                'the previous months' points are calculated by DSum
                If month = 1 Then
                    'Check for the possibility of having less than 5 partners for last month
                    If rs.EOF Then Exit For
                    Report_rptMensalMIS.Controls("lblParceiro" & rank).Caption = rs(0).Value
                    Report_rptMensalMIS.Controls(control).Caption = Format(rs(1).Value / 1000, "##,##0")
                    rs.MoveNext
                'Calculate partner's points for previous months
                Else
                    'Define which partner corresponds to the current Rank
                    partner = Report_rptMensalMIS.Controls("lblParceiro" & rank).Caption
                    'Error occurs here
                    Report_rptMensalMIS.Controls(control).Caption = Format(DSum("[TOTAL POINTS]", "tblTROCAS", "[Partner Name] = '" & partner & "' AND [DATA DO RELATORIO] = #" & CStr(Format(DateAdd("m", -month, Date), "mm/""01""/yyyy")) & "#") / 1000, "##,##0")
                End If
            Next rank
        Next month
    End If
End Sub
 

As commented above, error occurs at line 29.

Can anyone shed some light on this, please?

Thanks in advance,
Matt

[JKing]

Test your DSUM() logic to ensure that your criteria is producing a valid product. It's possible you are returning a null which in turn could be the problem.

[mhprol]

Hi JKing, thanks for your reply.

I had already tried debugging the code function by function, it all worked out up to DSum, hence the whole confusion.

Oddly enough, I turned off my PC on friday, went home, and sure as the sun shall rise the report was fully functional by monday morning, all on its own.

Thanks for your advice though, wish I could tell you what the solution was but I guess I'll leave that up to Microsoft's Reboot Factor.

-Matt