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
On Feb 11, 2016, at 8:53 AM, Khalid Tanweer email@example.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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 <firstname.lastname@example.org>
|Reply via web post||•||Reply to sender||•||Reply to group||•||Start a New Topic||•||Messages in this topic (2)|