Representing currency?

We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!Consider using the money datatype in SQL Server. It keeps 4 decimal places.
I'd never use real or float for money. It can handle very large numbers,
somewhat akin to my weekly salary. ;-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!|||Have you considered using the money data type?
J
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
> We have a pretty large financial application using SQL Server 2000 and an
> Access front-end. We're having trouble with rounding of currency values.
> In
> the database, we're using a Real datatype for money amounts. The problem
> is
> that in the VBA program we calculate a invoice header value for Invoice
> Total, and store it in a Real field. But when we do a SQL Sum() on the
> invoice line items, the value returned is "sometimes" off by a penny or
> so.
> Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
> In the VBA program, we store the database values in a variable of type
> Single.
> Any comments'
> Thanks!
>|||I'll differ from the other replies by saying don't use MONEY or SMALLMONEY
for monetary amounts. Use NUMERIC or DECIMAL instead. The money types have
no particular advantage (trivially you might save 1 byte in storage over the
equivalent DECIMAL) but they do have some significant problems with rounding
that can cause you to lose precision in calculations.
In any case REAL is an inexact numeric and so is probably totally unsuitable
for currency amounts.
--
David Portas
SQL Server MVP
--