[AccessD] Sum() returns #NUM!

Sum() and the other arithmetic functions by default return the same
datatype as are input.  So if you sum integers your answer is an
integer.  If you sum longs your answer is a long.  The sum of integers
may overflow the size of an integer which seems to be what you are
seeing.

Charles Wortz
Software Development Division
Texas Education Agency
1701 N. Congress Ave
Austin, TX 78701-1494
512-463-9493
CWortz at tea.state.tx.us



-----Original Message-----
From: Joe Rojas [mailto:JRojas at tnco-inc.com] 
Sent: Tuesday 2003 Jul 22 14:33
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Sum() returns #NUM!

Hi All,

Access 2000 (No SPs) ADP FE & SQL Server 7.0 BE

I created a stored procedure to return a recordset. One of the fields in
the record set is an integer value. I wanted to show the sum of this
integer field in the report header by adding a textbox and setting the
control value to =Sum([Total])

So far so good. My problem is sometimes the sum value in the report
header shows the true value and other times it return #NUM!. It almost
seems to be related to the number of records that are return, i.e. if
the number of records reaches a certain number the Sum function fails.

I am also trying to find out what type of value is returned by the Sum
function. Is it a variant? Integer? I could not find an answer on the
MSKB. Maybe Sum can only calculate to a certain value...I don't know...

Any suggestions?

Thanks!
Joe R.