calculation fields acts abnormal

[jaaferalakkal]

I am in a confusion that my access query calculated fields results abnormal where as the same query displays correct result in another query. Picture attached

i have a query1 field NjdActInvent:Nz([NJDinventory])+Nz([NjdPurchOrder])-Nz([njdSaleOrder])

[njdinventory] is 75 , [njdpurchOrder] is 295, [njdSaleOrder] is null but the result in [NjdActInvent] fiels shows the result 75295

i have query2 field
WhActInvent: Nz([whinventory])+Nz([whPurchOrder])-Nz([WHsaleOrder])

[whinventory] is 1435 , [whPurchOrder] is 298, [WHsaleOrder] is null but the result shows correctly as in [WhActInvent] 1137.

why the query1 filed shows wrong result. Anybody can help please.....

[Stewart Ross]

The values you are trying to add and subtract are being converted to text by the database engine's interpretation of the result returned by the non-null function (Nz) - which is why 75 + 295 is returning 75295. In your screenshots you will see that these values are left-aligned - a give-away that they have been converted to text. Numeric values always default to being shown right-aligned.

If you want to continue using Nz you should wrap each call with a conversion back to numeric form. The simplest way to do it would be to use the Val function:

WhActInvent: Val(Nz([whinventory]))+Val(Nz([whPurchOrder]))-Val(Nz([WHsaleOrder]))

-Stewart

[jaaferalakkal]

Val function was not useful in my case thats why i used clang function. My tables are linked with sql table . So the quantity field is taken as text in access , i made a query to convert that using clng function

[jaaferalakkal]

Thank you stewart....... it worked...