Khalid-
Sorry, I missed changing one TransactionID to TransactionDate. You should have been able to spot that.
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]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0) AS Balance
FROM [Clients Transaction]
WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));
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 11:58 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Again there is syntax error:
Syntax error in date in query expression 'TransactionDate <=#10# AND ([Clients Transaction].ClientCIN= 5'.
While repeatedly clicking on error message OK button the above figure between #6# go on changing, and this is value of TransactionID. Then the report opens.
Column for Balance is now showing data. But i assume its not in order for example see the top two rows
TransactionID TransactionDate AmountReceived Expenses Balance
6 01/01/14 Null 26,063 807
7 01/01/14 807 Null 807
Khalid
On Sunday, August 3, 2014 9:23 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Khalid-
I didn't notice that you have somehow lost the second parameter in all your DSum statements. Try this:
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]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionID] & "# AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0) AS Balance
FROM [Clients Transaction]
WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));
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 11:13 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
While opening the report got the following error message:
The Microsoft jet database engine cannot find the input table or query 'TransactionDate <=#01/01/14# AND [Clients Transaction].ClientCIN=5'. Make sure it exists and that its name is spelled correctly.
After long clicking on this message box OK button report opens but Balance column is showing zero value on each row. Although on report footer sum of "AmountReceived" and sum of "Expenses" is correct.
Khalid
On Sunday, August 3, 2014 8:03 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Kahlid-
Spotted an extra open paren in your first DSum and a few other anomalies:
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]),0)-NZ(DSum("Expenses","TransactionDate <= #" & [TransactionDate] & "# AND [Clients Transaction].ClientCIN = " & [ClientCIN]), 0) AS Balance
FROM [Clients Transaction]
WHERE ((([Clients Transaction].ClientCIN)=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN]));
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 7:55 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
Yahoo Groups
New - View Group Photos by Time or Location
Enjoy your group's photos beautifully arranged by date or place
.
__,_._,___
Tidak ada komentar:
Posting Komentar