Running total in a query/form

[WalkieTalkie]

I have a table called tblTransaction. Its fields are TransactionID(Autonumber), TransDate, Amount. I want to get a running balance eg:
TransactionID TransDate Amount Balance
1 1/1/05 10 10
2 2/1/05 6 16
3 3/1/05 -4 12

So simple, yet so difficult to achieve!
I have created a query with an expression to produce the [Balance] column:

Balance:DSum("Amount","tblTransaction","TransactionID <= "&TransactionID)

This works fine, except for when you want to add a record retrospectively - when ordered by date, the balance isn't right. So then I substituted TransDate for TransactionID, eg:

Balance:DSum("Amount","tblTransaction","TransDate <= # " & TransDate & "#")

With this I get a blank column. What am I doing wrong? I've fiddled with the '#' in case its a formatting problem, all to no avail. Is there a better way to do this? It seems like such an obvious thing to want to do, yet I'm having such trouble with it, am I going about it the wrong way?
By the way, I'm using Access 2003.
Any help will be greatly appreciated.

 

[willir]

Code looks fine except you might want to remove the extra space after the first number sign / octophorp...

Balance:DSum("Amount","tblTransaction","TransDate <= [COLOR=yellow blue]#"[/color] & TransDate & "#")

You can also use a function to perform this. Firstly, with a backend (tables and schema) / frontend (forms, reports and queries) database, aggregate functions such as DSum can be slow.

Here is a sample...

Function FindTotalAmount(datTransDate As Date) As Currency

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Sum(Amount) as TotalAmount FROM tblTransaction " _
& "WHERE TransDate <= #" & datTransDate & "#"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
FindTotalAmount = Nz(rst!TotalAmount, 0)

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

End Function

I improved the performance on my form significantly by moving away from the DSum approach.

For trouble shooting, you can view the value of strSQL (debug.Print strSQL, and CTRL-G to view the immediate / debugger window), and cut ans past the value in the Query Builder to view the results of the SQL statement.

Using a function allows you to call the function from just about anywhere -- on a form, in a report or in a query.

BTW, you do not specify an account in your example -- you only have one transaction table with no accounts -- correct (cash on hand, cash in the bank, amount owing, amount due...) ?

Richard

 

[RoyVidar]

If your regional settings dateformat differs from US format, you'll probably need to format the date to an unambiguous format:

[tt]..."TransDate <= # " & format$(TransDate, "mm\/dd\/yyyy") & "#")[/tt]

See International Dates in Access for more info.

Roy-Vidar

 

[WalkieTalkie]

Thanks, Roy-Vidar - that did the trick. And sorry it has taken me so long to reply. Thanks again for your help
Miranda

 

[TheAceMan1]

How are ya WalkieTalkie . . . . .

Have a look at : How to get a Running Sum in Queries How to get a Running Sum in Forms

See Ya! . . . . . .

 

[WalkieTalkie]

Hey, thanks for that link - how come I didn't find that when I did my original search? I need to search better, obviously. That's really helpful, everyone - as usual this site and the great people who contribute to it has provided a (several, in fact) solution(s).