Allen said:
[Currency] has none of the rounding issues inherent in
floating point types (Single and Double.)
I think you mean *accuracy* issues. I'll post rounding issues as a
potential *disadvantage* of CURRENCY i.e. it performs banker's rounding
which may not be appropriate to the OP.
The most obvious disadvantage of using CURRECY for non-monetary data is
that the observer will assume the data is monetary. We had an example
here a few days ago where it was suggested that a 'tax rate' column be
of type CURRENCY, plenty of scope for confusion there, I think.
Access 2000 introduced the Decimal type: a scalar number where you can
specify the number of decimal places. Unfortunately, it is not correctly
implemented, so even a basic sort fails, so I suggest you don't use this
type. Details:
http://allenbrowne.com/bug-08.html
Allen, I think you need to revisit the DECIMAL type.
For a sort to 'fail' the resultset must contain negative values AND the
sort order must be descending AND you must do the sorting at the engine
level. 'Wildly inaccurate' is a bit emotive when you consider the
pattern is entirely predictable and your "Nulls and zeros sort
unpredictably" is just plain wrong. The sort order is always the same:
first (wrong) the negative (less than zero) values are correctly sorted
in descending order, then positive values (greater or *equal* to zero)
correctly sorted in descending order, then (correct) the NULL values.
The Jet 4.0 spec for collation means NULLs will *always* appear last.
If you can post some code where the engine does not sort NULLs to the
end of the resultset then *that* would be news.
The most puzzling of your issues with the DECIMAL type is your "VBA has
no Decimal data type. Decimal as a subtype of Variant is vague and
inefficient". It's my understanding that DECIMAL is a scaled integer
type, using *multiple* integers, to support far greater precision and
scale than, say, CURRENCY and that each integer portion is not required
to be persisted in contiguous locations in memory, therefore it was
implemented as a *reference* type. Remember that DECIMAL lacks the
hardware support enjoyed by integer and floating point types i.e. it
was always going to be less efficient. To me, a Variant sub type is an
entirely reasonable implementation, rather than being the show-stopping
handicap you seem to think it is.
Let's use another example. The CHAR(N) data type (fixed length text)
directly maps to the VBA type String * N, being an intrinsic *value*
type i.e. more exacting and efficient that the VBA String *reference*
type. So, if having a non-vague and efficient VBA type is important to
you when choosing a data type in Access, why do I not see you posting
code examples using these types?
Allen, be honest here: is there a subtext to your 'throw enough mud'
approach to try to discredit the DECIMAL type?
I did see something the other day where you pointed out a bug (which I
have not tested) with the YESNO data type where an OUTER JOIN causes
Access to crash. Surely this is a more serious bug than the DECIMAL
issues you exaggerate? Do you have any plans to similarly recommend
that everyone avoids using the YESNO data type (in which you will have
my full support)?
Final question: having accepted, as you have, the accuracy ('rounding')
issues with the floating point data types, what do you do when the
client specifies five decimal places? Do you scale you own multiple
integers? store as CURRENCY and multiply by ten?
I can think of no better rebuttal than to point out that Jet natively
considers decimal data as being DECIMAL in nature e.g.
SELECT TYPENAME(0.5) FROM AnyTable
will return 'Decimal', and operating on a numeric value using a decimal
value causes it to be coerced to DECIMAL e.g.
SELECT TYPENAME(CDBL(0.1) / 0.5) FROM AnyTable
will also return 'Decimal'. If the DECIMAL type was as unusable as you
make it out to be we would all have heard about more problems five
years and three releases of Access down the line.
Jamie.
--
