ACCESS FEED: RE: [MS_AccessPros] Report Balance on each row should be correct on date order

Hello Khalid

The problem is that you (like me) live in the wrong part of the world. Our default short date format is dd/mm/yy, but SQL, being North-American-centric, assumes dates in xx/xx/xx form are actually mm/dd/yy, and it tries to interpret them that way.

Of course, there is no problem with 1/01/14, because that is unambiguous, but when you get to 7/02/14 (record 36), the problem starts…

This string in your SQL:
    "TransactionDate <= #" & [TransactionDate] & "# AND …
gets constructed like this:
    "TransactionDate <= #7/02/14# AND …

SQL interprets this as meaning "records before 2 July 2014", not "records before 7 February 2014".

To fix this, you need to explicitly format the dates in a way SQL understands (I prefer yyyy-mm-dd as it is less ambiguous). Replace every instance of:
    #" & [TransactionDate] & "#
with
    " & Format([TransactionDate], "\#yyyy-mm-dd\#") & "

For example:
    "TransactionDate <= #" & [TransactionDate] & "# AND …
becomes:
    "TransactionDate <= " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " AND …

The next problem is that the TransactionID values are not in chronological order. TransactionID #30 occurs AFTER #34, #36 and #39. This means that the criteria:
TransactionDate<=CurrentRecord.TransactionDate AND TransactionID<=CurrentRecord.TransactionID
will fail to include those three records.

To fix this, we need to break up TransactionDate<=CurrentRecord.TransactionDate into two separate criteria:
    1. TransactionDate<CurrentRecord.TransactionDate (and we don't care about TransactionID), and
    2. TransactionDate=CurrentRecord.TransactionDate AND TransactionID<=CurrentRecord.TransactionID

If either of these criteria is true, then the transaction is before the current record (inclusive) and should be included in the balance.

So now, every instance of:
    TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & "
needs to be replaced by:
  (TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#") & " AND TransactionID <= " & [TransactionID] & "))

This means your expression for the Balance column should be:
    Nz(DSum("AmountReceived","[Clients Transaction]",
            "(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#")
                & " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#")
                    & " AND TransactionID <= " & [TransactionID]
            & ")) AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]), 0)
    -Nz(DSum("Expenses","[Clients Transaction]",
            "(TransactionDate < " & Format([TransactionDate], "\#yyyy-mm-dd\#")
                & " OR (TransactionDate = " & Format([TransactionDate], "\#yyyy-mm-dd\#")
                    & " AND TransactionID <= " & [TransactionID]
            & ")) AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]), 0)
        AS Balance

Note also that it would be much faster to use subqueries instead of DSum calls. Below I have used an alias "t" for your [Clients Transaction] table (to reduce typing!) and replaced the Balance expression by the difference of two subqueries. I have also removed TotCash and TotExpenses as I assume they were just there for debugging, but you can add them back in using subqueries if you wish.

SELECT
    t.TransactionID,
    t.TransactionDate,
    t.TransactionType,
    t.ClientCIN,
    t.ClientName,
    t.ConsignmentNo,
    t.Cartons,
    t.Weight,
    t.Details,
    t.AmountReceived,
    t.Expenses,
    Nz((SELECT Sum([AmountReceived]) from [Clients Transaction] AS sr
        WHERE (sr.[TransactionDate]<t.[TransactionDate]
            OR (sr.[TransactionDate]=t.[TransactionDate] AND sr.[TransactionID]<=t.[TransactionID]))
        AND sr.[ClientCIN]=t.[ClientCIN]),0)
    -Nz((SELECT Sum([Expenses]) from [Clients Transaction] AS se
        WHERE (se.[TransactionDate]<t.[TransactionDate]
            OR (se.[TransactionDate]=t.[TransactionDate] AND se.[TransactionID]<=t.[TransactionID]))
        AND se.[ClientCIN]=t.[ClientCIN]),0)
    AS Balance
FROM [Clients Transaction] AS t
WHERE (t.ClientCIN=[Forms]![Clients Balance Sheet Dialog]![cboClientCIN])
ORDER BY t.TransactionDate, t.TransactionID;

All the best,
Graham [Access MVP 1996-2014]

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 5 August 2014 00:38
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Report Balance on each row should be correct on date order

John,

    Yet there is some flaw in Balance, from TransactionID 6 to 34 Balance is OK. See against TransactionID 36 Balance is not correct.

TransactionID TransactionDate AmountReceived Expenses Balance
  6 01/01/14 26,063 -26,063
7 01/01/14 807    -25,256
20 26/01/14    104 -25,360
21 27/01/14 250 -25,610
22 27/01/14    250 -25,860
34 30/01/14 7,000 -18,860
36 07/02/14 1,883 -20,795
39 20/02/14 350 -21,093
30 27/02/14 52 -25,912
44 27/02/14 1,555 -22,700

............ and more entries

Please look into it.

On Monday, August 4, 2014 4:45 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Ah, when you have multiple transactions on the same date, then you also need to sort on TransactionID.

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 TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotCash, DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]) AS TotExpense, NZ(DSum("AmountReceived","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [TransactionID] & " AND ([Clients Transaction].ClientCIN)= " & [ClientCIN]),0)-NZ(DSum("Expenses","[Clients Transaction]","TransactionDate <= #" & [TransactionDate] & "# AND TransactionID <= " & [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
http://www.viescas.com/
(Paris, France)

On Aug 4, 2014, at 12:15 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
    Sorry from me too, actually it was late night here when i received your last reply. Now there is no error in query.

But there is still some miscalculation in Balance i quote down few entries.

TransactionID TransactionDate AmountReceived Expenses Balance
6            01/01/14                 26,063 -25,256
7 01/01/14 807                -25,256
20            26/01/14              104    -25,360
21 27/01/14          250 -25,860
22 27/01/14       250 -25,860
34 30/01/14           7,000                  -18,860
36 07/02/14        1,883 -21,569
39
20/02/14        350 -21,093
30 27/02/14          52
-29,940
44 27/01/14        250 -25,860

............ and more entries

Please look into it.

regards,
Khalid

On Monday, August 4, 2014 12:25 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

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
http://www.viescas.com/
(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
http://www.viescas.com/
(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
http://www.viescas.com/
(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
http://www.viescas.com/
(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