INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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.

Jobs

Running Totals

Running Totals

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

CODE

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..

CODE

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

Regards,
KP

RE: Running Totals

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

Try:

CODE --> sql

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

Duane
Hook'D on Access
MS Access MVP

RE: Running Totals

(OP)
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,,
KP

RE: Running Totals

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

RE: Running Totals

(OP)
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

CODE

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	
65 
Thank you..

KP

RE: Running Totals

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

CODE --> SQL

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

Duane
Hook'D on Access
MS Access MVP

RE: Running Totals

(OP)
Thanks Duane,
for that..
kp

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!

Resources

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