Cumulative Numbers

[JJOHNS]

I have a database that I want to calculate Year-to-Date numbers. For now, if there is no record for a certain person, then the Year-to-Date number becomes equal to the daily number. That works. When there is already data in the database, so the program should go and add yesterday's number to today's, I get "Item Not Found in Collection"

Here's my code:

Dim Q As QueryDef, R As Recordset, SQLText

If DCount("TheDate", "qryRESULTSCount") = 0 Then
Me!YTD_NB = Me!NB

Else

SQLText = "SELECT tblRESULTS!SOB, tblRESULTS!TheDate, tblRESULTS!YTD_TIS, tblRESULTS!YTD_NB " _
& "FROM tblRESULTS WHERE tblRESULTS!SOB = '" & Me![SOB] & "' AND tblRESULTS!TheDate < " & Me![TheDate] & " ORDER BY tblRESULTS!TheDate DESC;"

Set Q = CurrentDb.CreateQueryDef("")
Q.SQL = SQLText
Set R = Q.OpenRecordset()

With Me.SOB
Me!YTD_NB = R!YTD_NB + Me!NB
End With

End If


Any ideas?

 

[TheAceMan1]

How are ya JJOHNS . . . . .

If I understand your code correctly, try this:

[blue]   Dim criteria As String
   
   criteria = "[SOB] = '" & Me![SOB] & "' AND TheDate < " & Me![TheDate]
    
   If DCount("TheDate", "qryRESULTSCount") = 0 Then
      Me!YTD_NB = Me!NB
   Else
      Me!YTD_NB = Me!NB + DMax("[YTD_NB]", "tblRESULTS", criteria)
   End If[/blue]

See Ya! . . . . . .