Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Running Totals

Running Totals

Running Totals

Hi all,
Just a question on running totals in a query
I have used this code below to give a running balance


RunningBalance: DSum("[Total]","Accounts","[Invoices]<=" & [Invoices]) 
However I want to add an expense field to this so we have an income and expense running balance.
I have an "expense" field (currency) in the table (Accounts)

I tried this, but did not work..


RunningBalance: DSum("[Total]","Accounts","[Invoices]<=" & [Invoices])-DSum("[Expense]","Accounts","[Invoices]<=" & [Invoices]) 


RE: Running Totals

I typically recomment a subquery rather than DSum() for performance reasons. The subquery will render the results readonly.


CODE --> sql

RunningBalance: DSum("[Total]-[Expense]","Accounts","[Invoices]<=" & [Invoices]) 

Hook'D on Access
MS Access MVP

RE: Running Totals

Hi Duane,
Many thanks for your reply.
I cut and pasted this code,into the query, but I am not able to get a running balance in the query. Its just blank

Do you have any other ideas? I googled sub-query and had a fiddle, but had no luck..
Thank you,,

RE: Running Totals

I tried this, but did not work..
Well, what hapenned exactly ?

RE: Running Totals

Hi guys,
Thank you for your replies.
Both "Total" and "expense" do have null values. When the query is run the Running Balance does not show any results.
Here is a copy of the query results


Invoices Expense	Total	RunningBalance
2		     $100.00	
4		     $200.00	
5		     $300.00	
6	$50.00		
8	$35.00		
13	$50.00		
14		     $45.00	
64		     $95.00	
Thank you..


RE: Running Totals

If one or both fields are null then the difference is null. You must convert Nulls to zero:


RunningBalance: DSum("Nz([Total],0)-Nz([Expense],0)","Accounts","[Invoices]<=" & [Invoices]) 

Hook'D on Access
MS Access MVP

RE: Running Totals

Thanks Duane,
for that..

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close