Running Totals 1

[integritycare]

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])

Regards,
KP

 

[dhookom]

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

Try:

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

Duane
Hook'D on Access
MS Access MVP

 

[integritycare]

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

 

[PHV]

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

 

[dhookom]

Are Total and Expense ever Null or do they both always have numeric values?

Duane
Hook'D on Access
MS Access MVP

 

[integritycare]

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	
65

Thank you..

KP

 

[dhookom]

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])

Duane
Hook'D on Access
MS Access MVP

 

[integritycare]

Thanks Duane,
for that..
kp