Sabtu, 13 Februari 2016

Re: [MS_AccessPros] Report to not show Zero balances



Add this to your Where clause:

And DSum("AmountReceived","[Clients Transaction]","TransactionID <=" & [TransactionID])-DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID]) <> 0


Sent from my iPad

On Feb 13, 2016, at 09:13, [MS_Access_Professionals] <> wrote:


In Sorting and Grouping of ReportI have:
Both having Group Header and Group Footer set to 'Yes'
On ClientCity Header I have Text box 'ClientCity'
And on ClientCIN Header I have Controls:
ClientCIN, ClientName, ClientBalance

ClientBalance--> Control Source--> =Nz([TotAmountReceived]-[TotExpenses])

Now as you asked how can I test that TotAmountReceived minus TotExpenses is not zero ?


---In, <JohnV@...> wrote :


Your SQL makes no sense.  You are limiting the output rows to those between January 1, 2012 and the current date, but you're including all transactions from Client Transactions and Expenses in your DSum expressions.  And this part of your WHERE clause is confusing:

AND ((Clients.ClientCity) Is Null)) OR ((Not (Clients.ClientCity) Is Null));

Why test ClientCity at all if you're including both rows that are Null and rows that are not?

If you want to eliminate all zero balance rows, then you need to test that TotAmountReceived minus TotExpenses is not zero.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)

On Feb 11, 2016, at 8:53 AM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <> wrote:

Hi All,

I have a report with following:
Record Source --> AllClientsBalanceSheetTodayQuery
SELECT [Clients Transaction].TransactionID, [Clients Transaction].TransactionDate, [Clients Transaction].ClientCIN, [Clients Transaction].ClientName, Clients.ClientCity, [Clients Transaction].AmountReceived, [Clients Transaction].Expenses, DSum("AmountReceived","[Clients Transaction]","TransactionID <=" & [TransactionID])-DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID]) AS Balance
FROM Clients INNER JOIN [Clients Transaction] ON Clients.ClientCIN = [Clients Transaction].ClientCIN
WHERE ((([Clients Transaction].TransactionDate) Between #1/1/2012# And Now()) AND ((Clients.ClientCity) Is Null)) OR ((Not (Clients.ClientCity) Is Null));
ClientBalance--> Control Source

I need that report should not display "ClientBalance" having Zero balance for any row, there are presently most of the balances in (-) minus figure which are displaying and i need them to display after any changes we make to not display ZERO balances of any ClientCIN.

Help required please.



Posted by: John Viescas <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)



Tidak ada komentar:

Posting Komentar