RUNNING SUM USING FAQ CODE

[John1Chr]

Greetings,

I'm having trouble using the running sum code in a query. It errors out on the code on Select Case rst.Fields(idName).Type and error is ITEM NOT FOUND IN THIS COLLECTION.

My query
SELECT t1.ACCEPTANCE_DT, t1.EXPDTR_AMT, t1.OBJ_CD, t1.TRANS_LINE_NBR, t1.TRANS_NBR, CDbl(RecRunSum("qry_tst_2","2/11/2010",[ACCEPTANCE_DT],"EXPDTR_AMT")) AS Expr2
FROM t1
WHERE (((t1.ACCEPTANCE_DT)>#8/1/2011#) AND ((t1.OBJ_CD)="2000"));

I want to get a running total by acceptance date for transactions greater than 8/1/2011 and object code 2000. I'm using the code in the FAQ. Hopefully, I didn't goof that up.

Public Function RecRunSum(qryName As String, idName As String, idValue, sumField As String)
'qryName - Name of the query calling this function
'idName - UniqueFieldName ... usually the primarykey
'idValue - The value of UniqueFieldName in the line above.
'sumField - The name of the field to runsum
Dim db As DAO.Database, rst As DAO.Recordset, subSum
Set db = CurrentDb()
Set rst = db.OpenRecordset(qryName, dbOpenDynaset)
'Find the current record via proper syntax for Data Type.
Select Case rst.Fields(idName).Type
Case dbLong, dbInteger, dbCurrency, dbSingle, dbDouble, dbByte
'Numeric
rst.FindFirst "[" & idName & "] = " & idValue
Case dbText
'Text
rst.FindFirst "[" & idName & "] = '" & idValue & "'"
Case dbDate
'Date
rst.FindFirst "[" & idName & "] = #" & idValue & "#"
Case Else
'Unknown data type returns Null
rst.MovePrevious
'Move to BOF
End Select
'Running Sum (subTotal) for each record occurs here.
Do Until rst.BOF
subSum = subSum + Nz(rst(sumField), 0)
rst.MovePrevious
Loop
qryRunSum = subSum
Set rst = Nothing
Set db = Nothing
End Function

 

[TheAceMan1]

How are ya John1Chr . . .

Double check the parameters for [blue]RecRunSum[/blue]. In your query you have:

qryName = [blue]"qry_tst_2"[/blue]
idName = [red]"2/11/2010"[/red] [green]UniqueFieldName ... usually the primarykey![/green]
idValue = [blue][ACCEPTANCE_DT][/blue]
sumField = [blue]"EXPDTR_AMT"[/blue]

Surely there's something wrong with the [blue]idName[/blue] your using. I'm sure you know ... backslashes are not allowed in names.

There's also a problem a problem at the end of the function just after the [blue]runsum[/blue] loop. No records would be returned!

[blue]change [red][b]qryRunSum[/b][/red] = subSum 
to     [green][b]RecRunSum[/b][/green] = subSum [/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[John1Chr]

Yeah at the end...crud. I was trying to sum up by date the total expenditures. Really, I would like to sum everything up by acceptance date. And really everything > 2/11/2010. I don't need transaction number or transaction line number. Yes, would have many of those per date so couldn't use date as unique key. I was hoping to filter out more of the query like object code 2000 in the query. Even though there are many transactions on that date sum it up so that it looks like:

ACCEPTANCE_DT OBJ_CD EXPDTR_AMT EXPR2(RecRunSum)

2/11/2011 2000 1000 1000
2/12/2011 2000 2000 3000
2/13/2011 2000 4000 7000