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

[wassimdaccache]

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

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

[missinglinq]

What Datatypes are vat_sum and sub_total?

Linq ;0)>

[Stewart Ross]

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:

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

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

[wassimdaccache]

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:

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

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,

[missinglinq]

It's just one of many quirks in Access! When used in a query, Nz() always returns a string!

Linq ;0)>