Subtraction Error. (1 Viewer)

[access2010]

I tried to change the value from Currency to Numeric, but the math error continues.

Original formula
=Sum(Nz([Stop_Current])-Nz([Current_Price]))

Second choice formula
=Sum(Nz([Stop_Current], 0)) - Sum(Nz([Current_Price], 0))

A = calculaction is wrong
B = the calculation in the DIFFERENCE field does not change.

Your suggestion will be appreciated.
Linda

 

[arnelgp]

Calculate the Diff on your query and use the Diff column on your Form:
modify your query:

SELECT Nz(Stop_Current,0)-Nz(Current_Price,0) AS Diff, *
FROM (SELECT Investments01_tbl.Stock_Name, Investments01_tbl.Symbol_Stock, Investments01_tbl.Investmentl_ID, Investments01_tbl.Stop_Current, Investments01_tbl.Current_Price, Investments01_tbl.Net_Share_Cost
FROM Investments01_tbl
ORDER BY Investments01_tbl.Stock_Name)  AS T;

The Control Source of Stop_Difference textbox will be Diff

 

[MarkK]

When you use the =Sum() function, you are summing the expression for all rows. This is more commonly used in a header or footer to show totals. Try =Nz([Stop_Current])-Nz([Current_Price]) in your Textbox.ControlSource

 

[access2010]

SELECT Nz(Stop_Current,0)-Nz(Current_Price,0) AS Diff, * FROM (SELECT Investments01_tbl.Stock_Name, Investments01_tbl.Symbol_Stock, Investments01_tbl.Investmentl_ID, Investments01_tbl.Stop_Current, Investments01_tbl.Current_Price, Investments01_tbl.Net_Share_Cost FROM Investments01_tbl ORDER BY Investments01_tbl.Stock_Name) AS T;
The Control Source of Stop_Difference textbox will be Diff

T.H.A.N.K. you Amelgp, for your suggestion.

 

[access2010]

When you use the =Sum() function, you are summing the expression for all rows. This is more commonly used in a header or footer to show totals. Try =Nz([Stop_Current])-Nz([Current_Price]) in your Textbox.ControlSource

T.H.A.N.K. you Mark for pointing out something that I did not Know, THE SUM FUCTION.