Problem with DECIMAL calculations in client side recordsets

[onedaywhen]

I finally got around to following up this thread:

http://groups.google.com/group/microsoft.public.access.queries/msg/7d73100337328fea

Using Jet 4.0, it seems that multiplying a DECIMAL literal value of a
certain numeric scale by another DECIMAL value (literal or otherwise)
of the same scale and returning the result to a client side ADO
recordset causes the result to be of the same scale, resulting in
truncation (being the engine's inherent rounding algorithm for the
Jet's DECIMAL type) i.e.

DECIMAL(i, n) x DECIMAL(j, n) = DECIMAL(k, n)

Example:

SELECT 5.9 * 22.1

returns 130.3, whereas the result should be 130.39.

Particularly bizarre is that

SELECT 0.09 * 0.09

returns zero!

The problem appears to be with the Recordset/Field object, rather than
the Jet engine e.g.

SELECT CBOOL(0.09 * 0.09 = 0.0081)

returns TRUE.

Here's some demo code:

Sub test()
'Kill "C:\DropMe.mdb"
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
cat.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"
Dim rs1
Set rs1 = CreateObject("ADODB.Recordset")
With rs1
.ActiveConnection = cat.ActiveConnection
.CursorLocation = 2 ' Server side cursor
.Source = _
"SELECT 'Server side cursor', 5.9 * 22.1"
.Open
End With
Dim rs2
Set rs2 = CreateObject("ADODB.Recordset")
With rs2
.ActiveConnection = cat.ActiveConnection
.CursorLocation = 3 ' Client side cursor
.Source = _
"SELECT 'Client side cursor', 5.9 * 22.1"
.Open
End With
MsgBox _
rs1.GetString & vbCr & rs2.GetString
End Sub

Jamie.

--

[onedaywhen]

onedaywhen wrote:
> Using Jet 4.0, it seems that multiplying a DECIMAL literal value of a
> certain numeric scale by another DECIMAL value (literal or otherwise)
> of the same scale and returning the result to a client side ADO
> recordset causes the result to be of the same scale, resulting in
> truncation (being the engine's inherent rounding algorithm for the
> Jet's DECIMAL type) i.e.
>
> DECIMAL(i, n) x DECIMAL(j, n) = DECIMAL(k, n)
>

> The problem appears to be with the Recordset/Field object, rather than
> the Jet engine e.g.
>
> SELECT CBOOL(0.09 * 0.09 = 0.0081)
>
> returns TRUE.
>

> .CursorLocation = 2 ' Server side cursor

I don't seem to be able to explain this (note all results are typed as
DECIMAL):

SELECT (1 + 0.1 - 0.1) / 10
returns 0.1 (correct)

SELECT (1 + 0.1 - 0.1) / 100
returns 0 (incorrect)

SELECT 1 / 100.01
0 (incorrect)

SELECT (1 + 0.1 - 0.1) / 100.01
0.00999900009999 (correct).

Madness!

Jamie.

--

[onedaywhen]

onedaywhen wrote:
> .CursorLocation = 2 ' Server side cursor

Just to clarify: stated behaviour is exhibited with a client side
cursor (ADODB.CursorLocationEnum.adUseClient = 3).

Jamie

--

[Stefan Hoffmann]

hi Jamie,

onedaywhen wrote:
>> DECIMAL(i, n) x DECIMAL(j, n) = DECIMAL(k, n)

Take a look at the MSDN Library, it's there somewhere explained.

>> SELECT CBOOL(0.09 * 0.09 = 0.0081)

Normally you don't compare floats for equality. You should use

(0.0081 - d) <= (0.09 * 0.09) <= (0.0081 + d)

with a sufficient small delta d.

> SELECT (1 + 0.1 - 0.1) / 100
> returns 0 (incorrect)

Try

(1.0 + 0.1 - 0.1) / 100.0

mfG
--> stefan <--

[onedaywhen]

Stefan Hoffmann wrote:
> >> DECIMAL(i, n) x DECIMAL(j, n) = DECIMAL(k, n)
> Take a look at the MSDN Library, it's there somewhere explained.

I've looked before and found nothing...

> >> SELECT CBOOL(0.09 * 0.09 = 0.0081)
> Normally you don't compare floats for equality. You should use
>
> (0.0081 - d) <= (0.09 * 0.09) <= (0.0081 + d)

They are not floats! They are DECIMAL values, scaled integers =
accurate, therefore should be reliable for equality comparisions.

Jamie.

--

[onedaywhen]

Stefan Hoffmann wrote:
> > SELECT (1 + 0.1 - 0.1) / 100
> > returns 0 (incorrect)
> Try
>
> (1.0 + 0.1 - 0.1) / 100.0

Wow, that is bizarre! Why is 100.0 DOUBLE FLOAT?

SELECT TYPENAME(100.0), TYPENAME(100.1), TYPENAME(101.0)

returns Double, Decimal, Long.

Jamie.

--

[Robert Morley]

I'll be damned! I did exactly what you suggested, and you're right, that's
exactly what I got too (in both Access XP and Access 2000). Access just
gets more bizarre by the day.

Rob
"onedaywhen" <jamiec...@xsmail.com> wrote in message
news:1163175952....@h54g2000cwb.googlegroups.com...

[onedaywhen]

onedaywhen wrote:

> Stefan Hoffmann wrote:
> > >> DECIMAL(i, n) x DECIMAL(j, n) = DECIMAL(k, n)
> > Take a look at the MSDN Library, it's there somewhere explained.
>
> I've looked before and found nothing...

I've reviewed all the MSDN returned when searching on the word
'decimal' and I can't see anything of relevance.

Can you give an outline of what this article says, please?

TIA,
Jamie.

--