Cumulative Totals with Dates (1 Viewer)

[V8Mustang]

Hello

I am trying to create a cumulative total for all costs going up day by day, but for some reason I cannot get it to work! I have butchered the code from the Microsoft support site but it keeps erroring, with errors like "Jet Database engine cannot find date" among others as I was playing around with it. I have created a total within the same query, but looking at another string this does not appear to be a problem, please help!!

RunTot: Format(DSum("Total","[Graph - Project Totals]","[DateID]<=" & [Date] & ""),"£0,000.00")

 

[Rich]

DSum is to slow use this function insted
Function RunningSum(Source As String, KeyName As String, KeyValue, _
FieldToSum As String)
'***********************************************************

' FUNCTION: RunSum()
' PURPOSE: Compute a running sum in a query.
' PARAMETERS:
' F - The form containing the previous value to
' retrieve.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.

' EXAMPLE: =RunSum("Table","ID",[ID],"Amount")
'***********************************************************
Dim rs As Recordset
Dim Result

On Error GoTo Err_RunningSum

' Get the form Recordset.
Set rs = CurrentDb().OpenRecordset((Source), dbOpenDynaset)

' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE

rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
GoTo Bye_RunningSum
End Select

' Compute the running sum.
Do Until rs.BOF
Result = Result + rs(FieldToSum)

' Move to the previous record.
rs.MovePrevious
Loop

Bye_RunningSum:
RunningSum = Result
Exit Function

Err_RunningSum:
Resume Bye_RunningSum

End Function

HTH