448,972 Members | 1,591 Online Need help? Post your question and get tips & solutions from a community of 448,972 IT Pros & Developers. It's quick & easy.

# Why does DSUM function is returning me a string not a number

 100+ P: 222 Expand|Select|Wrap|Line Numbers nz(DSum("[quantityindetail]*[purchaseprice]-[quantityindetail]*[purchaseprice]*DiscPurchasedetail*0.01","Purchasedetail","[purchaseid]= '" & [Purchaseid] & "'"),0) AS sub_total    nz(DSum("[vatvaluePurchasedetail]","Purchasedetail","[purchaseid]= '" & [Purchaseid] & "'"),0) AS vat_sum  I am running this two functions in a query. What I am trying to do is adding Expand|Select|Wrap|Line Numbers vat_sum + sub_total as grandtotal it is working but as string. example 25+5=255 How may I tell the DSUM to return me a number value in place of string ? any idea it would be appreciated Thank you in advance WASSIM S DACCACHE Nov 17 '08 #1
4 Replies

 Expert 2.5K+ P: 3,532 What Datatypes are vat_sum and sub_total? Linq ;0)> Nov 17 '08 #2

 Expert Mod 2.5K+ P: 2,545 Hi Wassim. It's not DSum that returns the string - it's the Nz function. You can convert each Nz string value back to a number using one of the type conversion functions as necessary: Expand|Select|Wrap|Line Numbers CCur(Nz(DSum..., 0)) ' convert to currency value CDbl(Nz(DSum..., 0)) ' convert to double-precision floating point CLng(Nz(DSum..., 0)) ' convert to long integer or you can use the Val function to take the numeric value of each string instead Expand|Select|Wrap|Line Numbers Val(Nz(DSum..., 0)) However, as Linq has pointed out you should ensure that the type of the variables or fields you are assigning these values to is correct. Access normally does type conversion 'on the fly' - if the result is assigned to a double-precision value, say, then Access would convert the Nz results to floating-point numbers before performing the sum. It suggests you have a text result type somewhere. -Stewart Nov 17 '08 #3

 100+ P: 222 Hi Wassim. It's not DSum that returns the string - it's the Nz function. You can convert each Nz string value back to a number using one of the type conversion functions as necessary: Expand|Select|Wrap|Line Numbers CCur(Nz(DSum..., 0)) ' convert to currency value CDbl(Nz(DSum..., 0)) ' convert to double-precision floating point CLng(Nz(DSum..., 0)) ' convert to long integer or you can use the Val function to take the numeric value of each string instead Expand|Select|Wrap|Line Numbers Val(Nz(DSum..., 0)) However, as Linq has pointed out you should ensure that the type of the variables or fields you are assigning these values to is correct. Access normally does type conversion 'on the fly' - if the result is assigned to a double-precision value, say, then Access would convert the Nz results to floating-point numbers before performing the sum. It suggests you have a text result type somewhere. -Stewart Thank you for your reply. I used the VAL () function and it is converting the string to number. but I didn't understand why it is replying me a string since i used the dsum function without the NZ () function also the dsum function is doing the sum of fields that datatypes are double. regards, Nov 18 '08 #4

 Expert 2.5K+ P: 3,532 It's just one of many quirks in Access! When used in a query, Nz() always returns a string! Linq ;0)> Nov 19 '08 #5 