My philosophy regarding Currency fields in Access is that they are great
for simple financial calulations, but nearly any meaningful calculation
puts the expression into the realm of floating point numbers. Most of
the time I end up using the Double type for calculations and a Currency
value to store the final results.
C.f.:
http://groups.google.com/group/comp.databases.ms-access/msg/7568a8ea7...
where exponentiation is involved in the solution.
Yes, you have to be careful Jet does not coerce values to DOUBLE
FLOATING POINT (SQL keyword in uppercase).
The one most commonly seen is dividing by an INTEGER e.g. (the
following examples use Access/Jet SQL while in ANSI-92 Query Mode):
SELECT TYPENAME(CCUR(123.45) / 100 -- returns 'Double'
Dividing by the equivalent a CURRENCY value yield the same:
SELECT TYPENAME(CCUR(123.45) / CCUR(100)) -- returns 'Double'
Multiplication is better:
SELECT TYPENAME(CCUR(123.45) * CCUR(0.01) -- returns 'Currency'
The case for DECIMAL is enhanced when you consider arithmetic on
values using a native decimal (lowercase) results in a DECIMAL e.g.
SELECT 54321 * 0.01 -- returns 'Decimal'
The key to this is knowing the native decimal vales are of type
DECIMAL e.g.
SELECT TYPENAME(123.45), TYPENAME(1.2345E2) -- returns 'Decimal' and
'Decimal'.
hence dividing a DECIMAL by an INTEGER results in a DECIMAL:
SELECT TYPENAME(123.45 / 100) -- returns 'Decimal'.
I think I am correct in saying the DECIMAL type is the
This is by design and is just as well because the CDEC() casting
function is broken in SQL (
http://support.microsoft.com/kb/225931) so
occasionally you have to get 'creative' at the cost of clarity e.g.
contrast:
SELECT 54321 / 12 AS result_value, TYPENAME(54321 / 12) AS
result_type
with:
SELECT 54321 / 0.12 * 0.01 AS result_value, TYPENAME(54321 / 0.12 *
0.01) AS result_type
or
SELECT 54321 * 8.33333333333333333333333333E-02, TYPENAME(54321 *
8.33333333333333333333333333E-02)
Beyond basic arithmetic, floating point results seem to be almost
inevitable
In SQL, raising a value to the power of another value
coerces the value to DOUBLE regardless of type of those values. Many
Jet, VBA, Access and UDF functions take the 'lowest common
denominator' approach of using Double, often because their
implementation predates VBA6 (which introduced the Decimal type) but
Decimal remains unpopular in VBA (often for no good reason) because it
is not an intrinsic/value type.
Even less exotic
operations quickly make Currency formats almost useless.
Careful of that slippery slope <g>. Jet is strongly-type by design and
perhaps the best approach is to deviate from the type in the reality
being modelled only when necessary.
Jamie.
--
.