By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,651 Members | 1,694 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,651 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
  1. nz(DSum("[quantityindetail]*[purchaseprice]-[quantityindetail]*[purchaseprice]*DiscPurchasedetail*0.01","Purchasedetail","[purchaseid]= '" & [Purchaseid] & "'"),0) AS sub_total
  2.  
  3.  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
  1. 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
Share this Question
Share on Google+
4 Replies


missinglinq
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
  1. CCur(Nz(DSum..., 0)) ' convert to currency value
  2. CDbl(Nz(DSum..., 0)) ' convert to double-precision floating point
  3. 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
  1. 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
  1. CCur(Nz(DSum..., 0)) ' convert to currency value
  2. CDbl(Nz(DSum..., 0)) ' convert to double-precision floating point
  3. 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
  1. 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

missinglinq
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

Post your reply

Sign in to post your reply or Sign up for a free account.