John,
I am getting syntax error while saving the query and it is highlighting "Expenses"
SELECT [Clients Transaction].TransactionID, [Clients Transaction].TransactionDate, [Clients Transaction].TransactionType, [Clients Transaction].ClientCIN, [Clients Transaction].ClientName, [Clients Transaction].ConsignmentNo, [Clients Transaction].Cartons, [Clients Transaction].Weight, [Clients Transaction].Details, [Clients Transaction].AmountReceived, [Clients Transaction].Expenses, DSum("AmountReceived","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotCash, DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]= " & [ClientCIN]),0)-NZ(DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]) AS Balance
FROM [Clients Transaction]
WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));
Khalid
On Sunday, August 3, 2014 4:47 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Khalid-
Try:
"TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN = " & [ClientCIN]
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 Aug 3, 2014, at 2:13 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi John,
I have a report with Record source "ClientsBalanceSheetQuery"
SELECT [Clients Transaction].TransactionID, [Clients Transaction].TransactionDate, [Clients Transaction].TransactionType, [Clients Transaction].ClientCIN, [Clients Transaction].ClientName, [Clients Transaction].ConsignmentNo, [Clients Transaction].Cartons, [Clients Transaction].Weight, [Clients Transaction].Details, [Clients Transaction].AmountReceived, [Clients Transaction].Expenses, DSum("AmountReceived","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionID <=" & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0) AS Balance
FROM [Clients Transaction]
WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));
Sorting and grouping is "TransactionID"
At present with this query results are perfect, but now the requirement is that Sort order should be TransactionDate and Balance on each row should be according to TransactionDate.
I have tried changing sort order to TransactionDate but results are not OK, also on the above query at DSum statements i tried to change "TransactionID <=" & [TransactionID]" with "TransactionDate <=" & [TransactionDate]" but that also did'nt worked.
please help.
Regards,
Khalid Tanweer
__._,_.___
Posted by: Khalid Tanweer <khalidtanweerburrah@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar