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 MS_Access_Professionals@yahoogroups.com, <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
On Feb 11, 2016, at 8:53 AM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:Hi All,I have a report with following:Record Source --> AllClientsBalanceSheetTodayQuerySELECT [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 BalanceFROM Clients INNER JOIN [Clients Transaction] ON Clients.ClientCIN = [Clients Transaction].ClientCINWHERE ((([Clients Transaction].TransactionDate) Between #1/1/2012# And Now()) AND ((Clients.ClientCity) Is Null)) OR ((Not (Clients.ClientCity) Is Null));----------ClientBalance--> Control Source=Nz([TotAmountReceived]-[TotExpenses])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.Regards,Khalid
Posted by: email@example.com
|Reply via web post||•||Reply to sender||•||Reply to group||•||Start a New Topic||•||Messages in this topic (3)|