Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

[mdlueck]

Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

This morning I am revisiting correctly handling Access Decimal field type values in VBA variables.

In the A2007 help for the Decimal Data Type, I see a warning as follows:

At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.

The VBA variable types of Single and Double seem to work correctly for Decimal numbers. Could someone please explain why this warning is given?

Is it that up to a certain point it will appear to work, and that the Decimal field type and the VBA Single/Double data types have different lower/upper bounds?

TIA!

 

[mdlueck]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

Aaahh... The variable storage size in memory of a:

Single --> 4 bytes
Double --> 8 bytes

And of the Decimal Access table field type is:

Decimal --> 12 bytes

So I would perceive that Decimal field types in Access are able to handle lower/higher numbers than even a VBA Double datatype may store. Correct?

So long as a Single / Double variable are capable of handling the values desired to be stored, should there be any further problems with paring up a Single / Double VBA variable with a Decimal Access DB field?

 

[gemma-the-husky]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

it depends what you are doing. it all comes down to precision of real numbers

an equality test for real numbers can fail, because of precision errors.

even some simple numbers - eg 0.1 cannot be represented perfectly with a binary expansion, so you have to be carefully when testing equality in queries etc.

for this reason "currency" is often a good choice, as it does guarantee absolute precision to 4dps

 

[mdlueck]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

it depends what you are doing.

for this reason "currency" is often a good choice, as it does guarantee absolute precision to 4dps

I need it for Part Weight. In SQL Server I have defined the col as:

[weight] [decimal](8, 4) NULL

In the Access FE temp table I have defined the column as:

Field Size: Decimal
Precision: 8
Scale: 4

And for VBA I will track the field value in a Single datatype variable. Nothing in Access performs calculations on the values.

In the BOM spreadsheet, weights get multiplied (by QtyPer) and rolled up (added / summed).

 

[Galaxiom]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

It isn't just testing for equality that brings Single and Double unstuck. Results of some operations have surprising rounding errors.

Try this expression: Int(90*0.7)

Even forcing Double doesn't help: Int(CDbl(90) * CDbl(.7))

But the correct answer is given with Decimal: Int(90 * CDec(.7))

 

[mdlueck]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

But the correct answer is given with Decimal: Int(90 * CDec(.7))

So perhaps be sure to wrap references with CDec() in that case?

And I will not need the outer Int() wrapper as a decimal point total will be what is acceptable.

So I would hope Excel (2007) can add and multiply decimal numbers correctly.

 

[Pat Hartman]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

As Dave already mentioned. If you don't need more than 4 decimal positions, Currency is your best bet. Don't confuse the Currency data type with the Currency format. The Currency data type can be formatted as standard and percent or any other valid numeric format.

 

[MarkK]

It isn't just testing for equality that brings Single and Double unstuck. Results of some operations have surprising rounding errors.

Try this expression: Int(90*0.7)

Even forcing Double doesn't help: Int(CDbl(90) * CDbl(.7))

But the correct answer is given with Decimal: Int(90 * CDec(.7))

I knew about errors representing decimals, but never saw such a clear display of that effect. Pretty cool!

 

[ChrisO]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

There are times when even the Currency data type will produce incorrect results when the Variant data type produces the correct results:-

Sub Test_TestIt()
    Dim A As Currency
    Dim B As Variant
    
    A = 396.5
    B = 396.5
    
    Debug.Print
    Debug.Print Round(0.19 * A, 2)
    Debug.Print Round(19 / 100 * A, 2)

    Debug.Print
    Debug.Print Round(0.19 * B, 2)
    Debug.Print Round(19 / 100 * B, 2)

End Sub

Since values are generally stored in tables and table fields can return Null then the data type of table fields is type Variant. It would therefore seem appropriate to use a Variant data type for the calculation and not try to force a typecast to another data type.

It may be that the literal division, 19 / 100, is handled better by a Variant but that is purely a guess.

I don’t know the reason for the above error but it does raise a question about the use of Currency under that particular circumstance. And, there may be many more similar circumstances.

Chris.

 

[spikepl]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

Before you get carried away, there is not much error here in representing decimals, but a lot of error in using the appropriate function when dealing with real numbers.

?Int(-0.000001)

So use Cint , which would give you what you'd expect.

 

[Pat Hartman]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

I expanded the code Chris posted to try to isolate the problem. Notice #9 produces an error also. The problem is caused by how Access determines the data type of intermediate work areas. The "COBOL Programmer's Guide" manual specified how to control what COBOL used as the definition for intermediate work areas and that allowed you to ensure that your calculations were accurate. Perhaps someone with the time to do the research could write a nice article on the subject telling us how Access defines intermediate work areas.

Sub Test_TestIt()
Dim A As Currency
Dim B As Variant
Dim C As Currency
Dim D As Variant

A = 396.5
B = 396.5

Debug.Print
Debug.Print "1-" & Round(0.19 * A, 2)
Debug.Print "2-" & Round(19 / 100 * A, 2)
C = 19 / 100
Debug.Print "3-" & Round(C * A, 2)
Debug.Print "4-" & Round(A * 19 / 100, 2)
Debug.Print "5-" & Round((19 / 100) * A, 2)


Debug.Print
Debug.Print "6-" & Round(0.19 * B, 2)
Debug.Print "7-" & Round(19 / 100 * B, 2)
D = 19 / 100
Debug.Print "8-" & Round(D * B, 2)
Debug.Print "9-" & Round(B * 19 / 100, 2)
Debug.Print "10-" & Round((19 / 100) * B, 2)

End Sub

 

[jdraw]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

A little off topic, but some interesting stuff on Rounding functions. Who would have thought of this "competition".

http://www.xbeat.net/vbspeed/c_Round.htm

 

[ChrisO]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

Thanks for the reply, Pat.

If I may try to reduce Pat’s reply a bit:-

Sub PatsDemoReduced()
    Dim A As Currency
    Dim B As Variant

    A = 396.5
    B = 396.5

    Debug.Print
    Debug.Print "1-" & Round(19 / 100 * A, 2)   ' Incorrect
    Debug.Print "2-" & Round(A * 19 / 100, 2)   ' Incorrect

    Debug.Print
    Debug.Print "3-" & Round(19 / 100 * B, 2)   ' Correct
    Debug.Print "4-" & Round(B * 19 / 100, 2)   ' Incorrect

End Sub

I’ll make a risky definition here…
---
It would appear that the data type of the entire result, as passed to the Round function, is controlled by the data type of the expression to the right of the multiplication.
AND…
For the entire result, as passed to the Round function, to be correct the data type of the expression to the right of the multiplication must be of data type Variant.
---

Notes:
1.
If someone wants to question if the Round function is doing this type or that type of rounding then it really doesn’t matter; whatever it is doing it is doing in all cases, hence it cancels out in the above test code.

2.
Though not stated in Access 2003 help, the Round function accepts a Variant data type. If the passed argument is Empty it will return 0 (zero). If the passed argument is Null it will return Null. In order for the Round function to not error on a passed Null argument it must accept an argument of data type Variant.

3.
I have not found any other data type, other than data type Variant, which produces the correct result under the above circumstance.

4.
As Pat has suggested, it is not simple and it would take some time to do the research.
A thread is still open on MSDN on the subject:-
http://social.msdn.microsoft.com/Fo...84ff238/#22501ec5-9ab7-4ee5-978f-53574ce4a413


Chris.

 

[mdlueck]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

As Dave already mentioned. If you don't need more than 4 decimal positions, Currency is your best bet. Don't confuse the Currency data type with the Currency format. The Currency data type can be formatted as standard and percent or any other valid numeric format.

Actually I had forgotten that Currency can handle out to four decimal positions. That sounds like the path of least resistance for this task.

So, in the VBA code in the spreadsheet I will also use the currency VBA variable type... but then what type of setting should I use to display the results of the calculations correctly? Obviously I do not want $Currency for Weight!

 

[mdlueck]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

Before you get carried away, there is not much error here in representing decimals, but a lot of error in using the appropriate function when dealing with real numbers.

?Int(-0.000001)

So use Cint , which would give you what you'd expect.

Oh to be back coding in REXX / Open Object Rexx ( http://www.oorexx.org/ ) which uses Decimal Arithmetic rather than Binary Arithmetic.

 

[gemma-the-husky]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

It isn't just testing for equality that brings Single and Double unstuck. Results of some operations have surprising rounding errors.

Try this expression: Int(90*0.7)

Even forcing Double doesn't help: Int(CDbl(90) * CDbl(.7))

But the correct answer is given with Decimal: Int(90 * CDec(.7))

ccur works fine also

Int(Ccur(90) * Ccur(.7))

 

[gemma-the-husky]

Re: Trying to understand the Access Decimal / VBA Single/Double "incompatibility"

interesting observations by ChrisO and Galaxiom

the truth is that if you need to handle floating point numbers accurately, then you need to give the procedure some thought.