Sum Query Incorrect

[NeonSky via AccessMonster.com]

Hello Everyone,

I am running a simple query that sums the values of multiple fields and it is
leading to some very wacky results. Please see below my sample table below.

FieldNameA FieldNameB FieldNameC
John 5 0

Next I create a new query, bring in the above table. I leverage a group by,
and in the total field for "SumOfFieldBandC" I select SUM rather than the
default group by.


FieldNameA SumOfFieldBandC[FieldNameB]+[FieldNameC])

This query is incorrectly returning

FieldNameA SumOfFieldBandC
John 50

Where "SumOfFieldBandC" should return "5", what gives?

Thanks for your help!

 

[Allen Browne]

Access is understanding the values as Text, and therefore concatenating the
text together instead of treating them as numeric values and performing
math.

The solution will be to find out why it thinks they are text, and make sure
it understands them as numbers. Here's an example:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[NeonSky via AccessMonster.com]

Hello Allen, Thanks for your response. Not sure what to do to make the below
work, there are functions mentioned on the link you provided though I am not
sure which is applicable. Thank you for your time and consideration.

Sum(([FieldB]+[FieldC])) AS SumOfFieldBandC

Access is understanding the values as Text, and therefore concatenating the
text together instead of treating them as numeric values and performing
math.

The solution will be to find out why it thinks they are text, and make sure
it understands them as numbers. Here's an example:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Hello Everyone,

[quoted text clipped - 21 lines]

Thanks for your help!

 

[NeonSky via AccessMonster.com]

I attempted the below to the same results....

Sum (CInt(([FieldB]+[FieldC]))) AS SumOfFieldBandC

Hello Allen, Thanks for your response. Not sure what to do to make the below
work, there are functions mentioned on the link you provided though I am not
sure which is applicable. Thank you for your time and consideration.

Sum(([FieldB]+[FieldC])) AS SumOfFieldBandC

Access is understanding the values as Text, and therefore concatenating the
text together instead of treating them as numeric values and performing

[quoted text clipped - 11 lines]

 

[Michel Walsh]

It seems that your fieldNameB and FieldNameC data type are STRING rather
than values:

? " hello " + " world "
hello world

? "5" + "0"
50

? 5 + 0
5


Note that I am surprised that SUM( string expression ) does not returns an
error, though.


Vanderghast, Access MVP

 

[Michel Walsh]

Try:

SUM( val( fieldb) + val( fieldc) ) AS sumOfFieldBandC


Your last solution was trying to convert to an integer AFTER the
concatenation, CInt( "50" ) is 50. You could have use CInt(fieldb) +
CInt(fieldC), though.


Vanderghast, Access MVP

I attempted the below to the same results....

Sum (CInt(([FieldB]+[FieldC]))) AS SumOfFieldBandC

Hello Allen, Thanks for your response. Not sure what to do to make the
below
work, there are functions mentioned on the link you provided though I am
not
sure which is applicable. Thank you for your time and consideration.

Sum(([FieldB]+[FieldC])) AS SumOfFieldBandC

Access is understanding the values as Text, and therefore concatenating
the
text together instead of treating them as numeric values and performing

[quoted text clipped - 11 lines]

Thanks for your help!

 

[NeonSky via AccessMonster.com]

Hello Michel, Not sure if you remember me though I remember you, you helped
me with a rather lengthy process I was working on last December. As always
thanks for being so helpful/informative. I appreciate it.

It seems that your fieldNameB and FieldNameC data type are STRING rather
than values:

? " hello " + " world "
hello world

? "5" + "0"
50

? 5 + 0
5

Note that I am surprised that SUM( string expression ) does not returns an
error, though.

Vanderghast, Access MVP

Hello Everyone,

[quoted text clipped - 21 lines]

Thanks for your help!

 

[Allen Browne]

What is FieldB?

Is it a field from a table?If so, open the table in design view and change
it from a Text field to a Number field.

Is it a calculated field in a query? If so, post the expression. Something
in the expression is causing Access to treat it as text. You can probably
verify that by opening that query, and seeing if JET left-aligns the column
(like text) or right-aligns it (as a number.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen, Thanks for your response. Not sure what to do to make the
below
work, there are functions mentioned on the link you provided though I am
not
sure which is applicable. Thank you for your time and consideration.

Sum(([FieldB]+[FieldC])) AS SumOfFieldBandC

Access is understanding the values as Text, and therefore concatenating
the
text together instead of treating them as numeric values and performing
math.

The solution will be to find out why it thinks they are text, and make
sure
it understands them as numbers. Here's an example:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html