decimal datatype conversion error

[Shane Hooper]

Hello, I have a SQL2005 database with an Access 2003 user interface. When I
try to convert the interface to Access97 I get the error: Invalid data type.
After lots of investigation I have found that any tables containing fields
with the decimal datatype is causing the problem. I don't want to change the
datatype as I then get rounding errors in the database. Is there any other
way to get around this problem?

Thanks, Shane.

 

[Van T. Dinh]

IIRC, Access97 doesn't have Decimal data type for Table Fields ... Thus, you
may have a bit of a problem here.

While not clearly stated, I think this article
http://support.microsoft.com/kb/104977 indicates Decimal data type doesn't
exist in A97.

A a test, try creating a new blank A97 database and linking a Table (with
decimal datatype Fields) and see if any error occurs. You may be able to
just this simple set-up to try different techniques to overcome the problem
....

 

[Rick Brandt]

IIRC, Access97 doesn't have Decimal data type for Table Fields ... Thus, you
may have a bit of a problem here.

While not clearly stated, I think this article
http://support.microsoft.com/kb/104977 indicates Decimal data type doesn't
exist in A97.

A a test, try creating a new blank A97 database and linking a Table (with
decimal datatype Fields) and see if any error occurs. You may be able to just
this simple set-up to try different techniques to overcome the problem

If you link to a table with a Decimal or Numeric field in Access 97 it will be
mapped to either Single, Double, or Text depending on the length and precision.
And yes this CAN cause problems with updates (rounding etc.).

If I am in a position to choose I always use the money type in SQL Server for
stuff that needs decimals if I want to manipulate them from an Access interface.

 

[Jamie Collins]

If I am in a position to choose I always use the money type in SQL Server for
stuff that needs decimals if I want to manipulate them from an Access interface.

<surprised> *Any* stuff that need decimals? Are you are aware of the
MONEY bug?:

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/af6861dc4d5e0705?

"If you will be multiplying and dividing [MONEY data] (especially in
the other order),
I'd recommend decimal(19,4), otherwise you might see serious
inaccuracies. Here's a simple example of where using money creates a
quick error of almost 1%..."

Jamie.

--

 

[Rick Brandt]

If I am in a position to choose I always use the money type in SQL
Server for stuff that needs decimals if I want to manipulate them
from an Access interface.

<surprised> *Any* stuff that need decimals? Are you are aware of the
MONEY bug?:

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/af6861dc4d5e0705?

"If you will be multiplying and dividing [MONEY data] (especially in
the other order),
I'd recommend decimal(19,4), otherwise you might see serious
inaccuracies. Here's a simple example of where using money creates a
quick error of almost 1%..."

Jamie.

But as I said, a Decimal(19,4) will be handled by Access 97 as Text and that
can cause real problems as well. In my case there have been VERY few times
where I needed fractional values that were not monetary values anyway so
money (currency in Access) has always been the logical choice.

 

[Terry Kreft]

That's not a bug, that's a perfectly predictable result based on the
datatype (all you have to know is whether the rule is to round or truncate
excess decimals), using the money datatype in this way may well introduce
bugs into your app but that's not the same as saying money is bugged.

Consider:-
declare @m money

set @m = 12.39

SET NOCOUNT ON
SELECT @m, convert(char(2), SQL_VARIANT_PROPERTY (@m, 'scale')),
convert(char(20), SQL_VARIANT_PROPERTY (@m, 'basetype'))
SELECT (@m/1000), convert(char(2), SQL_VARIANT_PROPERTY ((@m/1000),
'scale')), convert(char(20), SQL_VARIANT_PROPERTY (@m, 'basetype'))
SELECT (@m/1000)*1000, convert(char(2), SQL_VARIANT_PROPERTY
((@m/1000)*1000, 'scale')), convert(char(20), SQL_VARIANT_PROPERTY (@m,
'basetype'))
SET NOCOUNT OFF


Output:-

--------------------- ---- --------------------
12.3900 4 money


--------------------- ---- --------------------
..0123 4 money


--------------------- ---- --------------------
12.3000 4 money

Compare with:-
declare @d decimal(19,4)

set @d = 12.39

SET NOCOUNT ON
SELECT @d, convert(char(2), SQL_VARIANT_PROPERTY (@d, 'scale')),
convert(char(20), SQL_VARIANT_PROPERTY (@d, 'basetype'))
SELECT (@d/1000), convert(char(2), SQL_VARIANT_PROPERTY ((@d/1000),
'scale')), convert(char(20), SQL_VARIANT_PROPERTY (@d, 'basetype'))
SELECT (@d/1000)*1000, convert(char(2), SQL_VARIANT_PROPERTY
((@d/1000)*1000, 'scale')), convert(char(20), SQL_VARIANT_PROPERTY (@d,
'basetype'))
SET NOCOUNT OFF

Output:-

--------------------- ---- --------------------
12.3900 4 decimal


-------------------------- ---- --------------------
..012390000 9 decimal


------------------------------- ---- --------------------
12.390000000 9 decimal

and you can see the trick that the decimal datatype is pulling in order to
accomodate the extra decimal places, as money is restricted to 4 decimals it
cannot pull this trick.

--

Terry Kreft

If I am in a position to choose I always use the money type in SQL Server for
stuff that needs decimals if I want to manipulate them from an Access

interface.

<surprised> *Any* stuff that need decimals? Are you are aware of the
MONEY bug?:

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/af6861dc4d5e0705?

"If you will be multiplying and dividing [MONEY data] (especially in
the other order),
I'd recommend decimal(19,4), otherwise you might see serious
inaccuracies. Here's a simple example of where using money creates a
quick error of almost 1%..."

Jamie.

 

[Jamie Collins]

That's not a bug, that's a perfectly predictable result based on the
datatype (all you have to know is whether the rule is to round or truncate
excess decimals), using the money datatype in this way may well introduce
bugs into your app but that's not the same as saying money is bugged.

Agreed, 'bug' was the wrong word. The better term is 'feature'.

This issue demonstrates why proprietary features should be avoided
where possible, especially when more than one SQL product is involved.

The rounding rules for the DECIMAL type are defined in the ANSI SQL-92
standard, against which SQL Server's own implementation of DECIMAL may
be compared for compliance. MONEY is proprietary to SQL Server so the
spec can be made up to fit the implementation if they like (can even
change for each new release) and certain bugs can indeed become
'features'.

Microsoft isn't even compliant across its own SQL products e.g. Jet's
CURRENCY has different rounding rules to SQL Server's MONEY e.g.

SELECT 12.39 AS decimal_value,
CCUR(12.39) AS curreny_value,
(decimal_value / 1000) * 1000 AS decimal_result,
(curreny_value / 1000) * 1000 AS currency_result_as_float,
(curreny_value * 0.001) * 1000 AS currency_result_as_decimal

Output:

decimal_result
-----------
12.39

currency_result_as_float
-----------
12.39 -- SQL Server's MONEY returns 12.3000

currency_result_as_decimal
-----------
12.39 -- SQL Server's MONEY returns 12.3000


Best to stick to the ANSI standard DECIMAL, I feel.

Jamie.

--

 

[Jamie Collins]

But as I said, a Decimal(19,4) will be handled by Access 97 as Text and that
can cause real problems as well.

Could be time to give your client notice that you will be following
MSFT's lead in removing support for Jet 3.n and Office97.

In my case there have been VERY few times
where I needed fractional values that were not monetary values anyway so
money (currency in Access) has always been the logical choice.

Each to their own, I suppose, but if I encountered two columns,
tax_amount and tax_rate, both of which were MONEY, I would not have an
overwhelming urge to congratulate the designer on their great logical
choice ;-)

Jamie.

--

 

[Terry Kreft]

Feature, is just the word I'd use.

Microsoft making it up as they go along _and_ being incosistent while they
do it, I can barely stand the shock !! <g>.