Access Beginner => "sum operation cannot take a varchar data type as an argument"

[markx]

Hello,

Does anyone here has an idea what this could mean and how to fix it?

ODBC -- call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate
operation cannot take a varchar data type as an argument. (#409)

I try to make a "view/totals/sum" on a field in which there are only numeric
values (gross amounts).
This field comes from another query that was performed on an ODBC linked
table.

Thanks a lot for your input,
Mark

 

[Jeff Boyce]

Mark

It sounds to me like you are trying to add or average character data --
e.g., what does it mean to take the average of a field that holds [City]?!

 

[markx]

Thanks Jeff,

That's what I thought at the beginning, but I can quite confidently assure
you that there are only numeric values (f. ex. 2315.23)
Is there any test that I can put in place in order to get all the records
that are non-numeric (in this exemple "varchar")?

Another thing I thought about is that maybe something wrong happens with
"zero" values or maybe regional preferences do not recognize "." (before
decimals) and prefer instead ","? Again, what is the best way to check this?

Thanks a lot,
Mark

Mark

It sounds to me like you are trying to add or average character data --
e.g., what does it mean to take the average of a field that holds [City]?!

--
Regards

Jeff Boyce
<Access MVP>

Hello,

Does anyone here has an idea what this could mean and how to fix it?

ODBC -- call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate
operation cannot take a varchar data type as an argument. (#409)

I try to make a "view/totals/sum" on a field in which there are only numeric
values (gross amounts).
This field comes from another query that was performed on an ODBC linked
table.

Thanks a lot for your input,
Mark

 

[Jeff Boyce]

Mark

There's a difference between storing numbers, and storing characters that
happen to be digits. For example, US zip codes are numbers ... well,
actually, they are characters, since you'd never "average" a set of zip
codes, nor do any other kind of math with them.

Your error message mentions varchar, a CHARACTER data definition.

Jeff Boyce
<Access MVP>

Thanks Jeff,

That's what I thought at the beginning, but I can quite confidently assure
you that there are only numeric values (f. ex. 2315.23)
Is there any test that I can put in place in order to get all the records
that are non-numeric (in this exemple "varchar")?

Another thing I thought about is that maybe something wrong happens with
"zero" values or maybe regional preferences do not recognize "." (before
decimals) and prefer instead ","? Again, what is the best way to check this?

Thanks a lot,
Mark

Mark

It sounds to me like you are trying to add or average character data --
e.g., what does it mean to take the average of a field that holds [City]?!

--
Regards

Jeff Boyce
<Access MVP>

Hello,

Does anyone here has an idea what this could mean and how to fix it?

ODBC -- call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate
operation cannot take a varchar data type as an argument. (#409)

I try to make a "view/totals/sum" on a field in which there are only numeric
values (gross amounts).
This field comes from another query that was performed on an ODBC linked
table.

Thanks a lot for your input,
Mark

 

[david epsom dot com dot au]

You need to cast the varchar field to a numeric format
before you do the sum.

In an access query you might do something like this:

nField: val([fieldname])
sum

(david)

 

[markx]

Thanks a lot to both of you, guys!

SELECT ..., Sum(Val(

.[field])) AS SumOf[field]
I've added to my query "val" expression (as above) and it worked...

BTW: Is there somewhere on the net (or inside the MS Access Help file) a
comprehensive list/tutorial on MS Access SQL expressions?
I've noticed that it can vary quite a lot from environment to environment...

Thanks once again,
Mark

You need to cast the varchar field to a numeric format
before you do the sum.

In an access query you might do something like this:

nField: val([fieldname])
sum

(david)

Hello,

Does anyone here has an idea what this could mean and how to fix it?

ODBC -- call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average
aggregate operation cannot take a varchar data type as an argument.
(#409)

I try to make a "view/totals/sum" on a field in which there are only
numeric values (gross amounts).
This field comes from another query that was performed on an ODBC linked
table.

Thanks a lot for your input,
Mark