Adding two Sums together, with Nz

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query built upon other queries. In [qryContactsScores] and
[qryLeadsScores], I have a field which calculates the sum of a value, and
another field that runs a Nz expression on the first field.

When I run a third query to add together the two fields with the Nz
expression, it treats the "+" like an "&" sign. So instead of 2+2=4, I get
2+2=22. Any idea what I'm doing wrong?
 
What is the field type of the fields that you using for the sum?

It sound like it treating it as text fields, you can convert them to numbers
while adding them

Select Cdbl([Field1Name]) + Cdbl([Field2Name]) As NewField From TableName
 
they are number fields in the original table.

I did finally figure out a way around it. Instead of using
Nz([SumOfLeadsScores],0) I used IIf([SumOfLeadsScores] is Null, 0,
[SumOfLeadsScores])

It seems to work, but I don't know if that's the best way to do it.

Ofer said:
What is the field type of the fields that you using for the sum?

It sound like it treating it as text fields, you can convert them to numbers
while adding them

Select Cdbl([Field1Name]) + Cdbl([Field2Name]) As NewField From TableName

--
\\// Live Long and Prosper \\//
BS"D


kdagostino said:
I have a query built upon other queries. In [qryContactsScores] and
[qryLeadsScores], I have a field which calculates the sum of a value, and
another field that runs a Nz expression on the first field.

When I run a third query to add together the two fields with the Nz
expression, it treats the "+" like an "&" sign. So instead of 2+2=4, I get
2+2=22. Any idea what I'm doing wrong?
Click to expand...
Click to expand...
 
They both are the same thing, the NZ and the IIf should give the same resault.

--
\\// Live Long and Prosper \\//
BS"D


kdagostino said:
they are number fields in the original table.

I did finally figure out a way around it. Instead of using
Nz([SumOfLeadsScores],0) I used IIf([SumOfLeadsScores] is Null, 0,
[SumOfLeadsScores])

It seems to work, but I don't know if that's the best way to do it.

Ofer said:
What is the field type of the fields that you using for the sum?

It sound like it treating it as text fields, you can convert them to numbers
while adding them

Select Cdbl([Field1Name]) + Cdbl([Field2Name]) As NewField From TableName

--
\\// Live Long and Prosper \\//
BS"D


kdagostino said:
I have a query built upon other queries. In [qryContactsScores] and
[qryLeadsScores], I have a field which calculates the sum of a value, and
another field that runs a Nz expression on the first field.

When I run a third query to add together the two fields with the Nz
expression, it treats the "+" like an "&" sign. So instead of 2+2=4, I get
2+2=22. Any idea what I'm doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top