The issue with DSUM: not calculating as expected

[sibbbra]

hi
I have the DSum expression as below:
Balance_Customer: Val(DSum("([Amount_Cash_Customer]","Orders","Customer_ID = " & [Customers].[Customer_ID] & " and Order_Date <= #" & [Order_Date] & "#"))

but it is not calculating accurateley and there are differences in results, when Order ID in not in continuity, i.e when enteries are made for the back dates.
any help ?

 

[arnelgp]

you need to use a Query since your ID is not consistent with the Order Date:

select *, Format([Order Date], "yyyymmdd") & Format(ID, "00000") As Expr1 from yourTable;

save the query and use it as your Recordsouce of your form:

to lookup:

Balance_Customer: Val(DSum("([Amount_Cash_Customer]","QueryName","Customer_ID = " & [Customers].[Customer_ID] & " and Expr1 <= '" & Format(Order_Date, "yyyymmdd") & Format(ID, "00000") & "'") & "")

 

[The_Doc_Man]

OK, in your report, what is the sort order you have imposed, and how did you impose it?

The reason I ask is because offering Access a Query with an Order By clause doesn't affect report order. You have to impose sorting and grouping through the report's interface.

I'll suggest that in fact DSum is accurately answering your question, but you may have not asked the question you thought you were asking.

 

[sibbbra]

you need to use a Query since your ID is not consistent with the Order Date:

select *, Format([Order Date], "yyyymmdd") & Format(ID, "00000") As Expr1 from yourTable;

save the query and use it as your Recordsouce of your form:

to lookup:

Balance_Customer: Val(DSum("([Amount_Cash_Customer]","QueryName","Customer_ID = " & [Customers].[Customer_ID] & " and Expr1 <= '" & Format(Order_Date, "yyyymmdd") & Format(ID, "00000") & "'") & "")

it gave Error in results

 

[sibbbra]

T

OK, in your report, what is the sort order you have imposed, and how did you impose it?

The reason I ask is because offering Access a Query with an Order By clause doesn't affect report order. You have to impose sorting and grouping through the report's interface.

I'll suggest that in fact DSum is accurately answering your question, but you may have not asked the question you thought you were asking.

thanx Doc. Here is the code...

FROM [Customer_Orders Query]
WHERE ((([Customer_Orders Query].Order_Date) Between [forms]![Customer Orders By Customer Report].[begdate] And [forms]![Customer Orders By Customer Report].[enddate]) AND (([Customer_Orders Query].Customer_ID) Like [forms]![Customer Orders By Customer Report].[managerCombo]))
ORDER BY [Customer_Orders Query].Order_Date;

 

[arnelgp]

The reason I ask is because offering Access a Query with an Order By clause doesn't affect report order.

it does seem to be in "order" (by date) the report.

post a sample db and i will show, a suggestive, result.

 

[Gasman]

It is a little worrying if you have to include a Val() function in that DSum() ?

 

[cheekybuddha]

It looks like you use DD/MM/YYYY date format in your local regional settings.

You will need to convert the date in your DSum() criteria to either US or ISO format:

Balance_Customer: DSum("[Amount_Cash_Customer]","Orders","Customer_ID = " & [Customers].[Customer_ID] & " and Order_Date <= #" & Format([Order_Date], "yyyy-mm-dd") & "#")

 

[sibbbra]

it does seem to be in "order" (by date) the report.

post a sample db and i will show, a suggestive, result.

thanx for helping me out.
here is the db attached.
goto the form: Customer Orders By Customer Report. then there is only one customer Jerry. put date from 1 jan 2023 to Today. then the report ll populate I am having problem in.

 

[sibbbra]

It looks like you use DD/MM/YYYY date format in your local regional settings.

You will need to convert the date in your DSum() criteria to either US or ISO format:

Balance_Customer: DSum("[Amount_Cash_Customer]","Orders","Customer_ID = " & [Customers].[Customer_ID] & " and Order_Date <= #" & Format([Order_Date], "yyyy-mm-dd") & "#")

Not helped. nothing happened. its the same. anyhow thanx for replying

 

[Gasman]

Not what I see?

Balance_Customer: Val(DSum("([Amount_Cash_Customer]+[Bank1_In]+[Bank2_In]+[Bank3_In]+[Bank4_In]+[bank5_In]+[bank6_In]+[Item_Amount])-([Amount_Sale]-[Amount_Purchase]-[Discount])-[Previous_Amount_Customer]","Orders","Customer_ID = " & [Customers].[Customer_ID] & " and Order_Date <= #" & [Order_Date] & "#"))

 

[arnelgp]

check if the report is correct.

 

[sibbbra]

check if the report is correct.

Not as expected. The second entry should be 342001 as 400000 has been credited. it gave false results. plz help

 

[The_Doc_Man]

If this is your code on which you based the report, your problem is that the ORDER BY is ignored by that report. To do proper ordering in a report you use the ribbon-based design feature that allows you to specify grouping and sorting. You can have the most comprehensive ORDER BY clause ever devised by man, but Access reports will change things around. Therefore, any computation based on that query's assumed order will not be right.

Also, your LIKE operation might as well be an equals-sign because you have a LIKE relational operator with no visible wildcards.

Also, where you use [Customer_Orders_Query] (other than in the FROM clause), you can omit it. Since you have only one table listed for FROM, there is only one possible source for it.

FROM [Customer_Orders Query]
WHERE (((Order_Date) 
    Between [forms]![Customer Orders By Customer Report].[begdate] And [forms]![Customer Orders By Customer Report].[enddate]) AND                  ((Customer_ID) Like [forms]![Customer Orders By Customer Report].[managerCombo]))
ORDER BY Order_Date;

If this is a dynamically created query, there are a couple of other things you can do to optimize it, like concatenating the [begdate] and [enddate] as date texts prior to executing the query.

 

[sibbbra]

nothing has helped

If this is your code on which you based the report, your problem is that the ORDER BY is ignored by that report. To do proper ordering in a report you use the ribbon-based design feature that allows you to specify grouping and sorting. You can have the most comprehensive ORDER BY clause ever devised by man, but Access reports will change things around. Therefore, any computation based on that query's assumed order will not be right.

Also, your LIKE operation might as well be an equals-sign because you have a LIKE relational operator with no visible wildcards.

Also, where you use [Customer_Orders_Query] (other than in the FROM clause), you can omit it. Since you have only one table listed for FROM, there is only one possible source for it.

FROM [Customer_Orders Query]
WHERE (((Order_Date)
    Between [forms]![Customer Orders By Customer Report].[begdate] And [forms]![Customer Orders By Customer Report].[enddate]) AND                  ((Customer_ID) Like [forms]![Customer Orders By Customer Report].[managerCombo]))
ORDER BY Order_Date;

If this is a dynamically created query, there are a couple of other things you can do to optimize it, like concatenating the [begdate] and [enddate] as date texts prior to executing the query.

nothing has helped yet

 

[arnelgp]

i added Balance2, i don't want to touch your original computation.
see if this is what you need.

 

[Gasman]

Here is how I did it. Though I had positive and negative values for Amount.

SELECT Emails.ID, Emails.TransactionDate, Emails.CMS, Emails.Client, Emails.Amount, DSum("[Amount]","Emails","[CMS]=" & [CMS] & " AND ID < " & [ID]) AS PrevBal, [Amount]+nz([PrevBal],0) AS NewBal
FROM Emails
ORDER BY Emails.ID, Emails.TransactionDate, Emails.CMS;

I would get your running balance correct first with just the fields needed, then add all the other fields you need for the report.


That produced.

 

[sibbbra]

i added Balance2, i don't want to touch your original computation.
see if this is what you need.

oh, nothing good came out. the problem of flase calculations is there

 

[sibbbra]

Here is how I did it. Though I had positive and negative values for Amount.

SELECT Emails.ID, Emails.TransactionDate, Emails.CMS, Emails.Client, Emails.Amount, DSum("[Amount]","Emails","[CMS]=" & [CMS] & " AND ID < " & [ID]) AS PrevBal, [Amount]+nz([PrevBal],0) AS NewBal
FROM Emails
ORDER BY Emails.ID, Emails.TransactionDate, Emails.CMS;

I would get your running balance correct first with just the fields needed, then add all the other fields you need for the report.


That produced.
View attachment 107663

i have attached the sample DB above. Kindly work it out on Balance as indicated in the above posts. much appreciated...

 

[arnelgp]

oh, nothing good came out. the problem of flase calculations is there

what is wrong with the calculation, you said it is 342001 is the second line?