Avg() in passthrough query returns string

[HoustonRich]

I am developing application in MS Access 2002. I have a passthrough query to
retrieve data from SQL Server 2000 SP4 (8.00.2039) with count, avg and sum
functions. All three functions work properly against floating datatypes in
SQL Server. However, the avg and sum functions return strings against numeric
datatypes in SQL Server, even though the strings correctly portray the
average or the sum. Tried to use SUM(BOARDINGS * 1.0) to force to number
without success. I use Microsoft SQL Server ODBC Driver Version 03.85.1117.
How I can get avg and sum back as numeric datatypes.

 

[giorgio rancati]

Hi HoustonRich,

It's an old Acces bug with SUM() or AVG() function on datatype decimal or
numeric.

this is the workaround
----
Select Convert(numeric(28,4), Sum(BOARDINGS)) AS SumOfBOARDINGS
From Table
----
change the scale (4 in example) with your decimals but don't exceed the
precision 28

The Sum() or AVG() function on numeric(x,y) or decimal(x,y) returns the
numeric(38,y) or decimal(38,y).
Access doesn't know it

bye

 

[HoustonRich]

Yes, I understand now. Seem more expedient to used int, smallint, etc in SQL
Server database, and avoid decimal and numeric datatypes.

Hi HoustonRich,

It's an old Acces bug with SUM() or AVG() function on datatype decimal or
numeric.

this is the workaround
----
Select Convert(numeric(28,4), Sum(BOARDINGS)) AS SumOfBOARDINGS
From Table
----
change the scale (4 in example) with your decimals but don't exceed the
precision 28

The Sum() or AVG() function on numeric(x,y) or decimal(x,y) returns the
numeric(38,y) or decimal(38,y).
Access doesn't know it

bye
--
Giorgio Rancati
[Office Access MVP]

I am developing application in MS Access 2002. I have a passthrough query
to
retrieve data from SQL Server 2000 SP4 (8.00.2039) with count, avg and sum
functions. All three functions work properly against floating datatypes in
SQL Server. However, the avg and sum functions return strings against
numeric
datatypes in SQL Server, even though the strings correctly portray the
average or the sum. Tried to use SUM(BOARDINGS * 1.0) to force to number
without success. I use Microsoft SQL Server ODBC Driver Version
03.85.1117.
How I can get avg and sum back as numeric datatypes.