[AccessD] Sum() returns #NUM!

Thanks for the replies!
It turns out that it was an overflow issue. The data type in SQL Server is
int, which is not compatible with the integer data type in Access.
I just changed the data type in Access to long and the problem is fixed!

Thanks for the help!

Joe R.

-----Original Message-----
From: Gary Kjos [mailto:garykjos at hotmail.com]
Sent: Tuesday, July 22, 2003 3:57 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Sum() returns #NUM!

Hi Joe,

Might you have nulls in that field?

You may want to put a nz funcion in their to convert nulls to zeros.

Gary Kjos
garykjos at hotmail.com





>From: Joe Rojas <JRojas at tnco-inc.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem solving'"
><accessd at databaseadvisors.com>
>Subject: [AccessD] Sum() returns #NUM!
>Date: Tue, 22 Jul 2003 15:33:14 -0400
>
>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.
>
>
>
>
>This electronic transmission is strictly confidential to TNCO, Inc. and
>intended solely for the addressee. It may contain information which is
>covered by legal, professional, or other privileges. If you are not the
>intended addressee, or someone authorized by the intended addressee to
>receive transmissions on behalf of the addressee, you must not retain,
>disclose in any form, copy, or take any action in reliance on this
>transmission. If you have received this transmission in error, please
>notify
>the sender as soon as possible and destroy this message. While TNCO, Inc.
>uses virus protection, the recipient should check this email and any
>attachments for the presence of viruses. TNCO, Inc. accepts no liability
>for
>any damage caused by any virus transmitted by this email.
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please notify
the sender as soon as possible and destroy this message. While TNCO, Inc.
uses virus protection, the recipient should check this email and any
attachments for the presence of viruses. TNCO, Inc. accepts no liability for
any damage caused by any virus transmitted by this email.