Access Decimal Type and VBA

[pvdg42]

I was asked this question last evening and I'm hoping somebody here has a
workable answer (because I sure don't ):
Given an Access table with Decimal fields, a recordset is created in VBA to
"massage" the data (actually, check the data against another table looking
for certain congruencies). The problem is that VBA doesn't seem to recognize
the decimal fields properly and VBA's Currency type appears to be
incompatible.
So, is there any way to convert Access Decimal to a type VBA recognizes
without mangling the data?

Feel free to point me to a better group for this question if it's off topic
here.

 

[Guest]

I don't know how much this helps you, but I believe this are "issues" when
using the Decimal format for your numbers. It's my understanding that you
should use Currency when using decimal places, however it's limited to only
4.

If you're comparing numbers and you need to go deeper than 4 decimal places,
you're going to have to use Single or Double. I believe these are "accurate"
to something like 7 (you'd have to check this) places before the floating
point get's the better of it.

What you could do is to force both numbers you're comparing to round up
(several decimal deep would still do the trick). This would allow more
accurate comparison when using Single or Double.

Another thought may be to artificially get your numbers by
multiplying-dividing your base numbers by the decimal factor you're looking
for. (ie. an AfterUpdate event takes the 3.141592 the user entered and
multiplies it by 1,000,000 to get 3,141,592) You could do this for both
sides of numbers you're trying to compare, then you can compare them without
decimal places.

HTH

Aaron G
Philadelphia, PA

 

[Allen Browne]

As you found there is no decimal data type in VBA, so you must use a
variable of type Variant. You can force the Variant to type decimal with
CDec().

However, JET is unable to perform very basic operations reliably with this
data type. More info:
http://allenbrowne.com/bug-08.html