decimal display

[Guest]

Why, even if I change the decimal place setting to two in the table, does
Access always round up or down to the nearest dollar in a number field set to
currency. Is there a way to set a field's display to currency but still
allowing the display to show the actual numbers after the decimal (Such as
$13.13 instead of having it round down to $13.00). Thanks.

 

[Jeff Boyce]

What is the underlying data type of the field? Based on the results you are
reporting, I'm guessing the data is "integer", even though you are telling
Access to display it as "currency".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Actually, it is set to "long integer." What should it be set to in order to
allow the display of actual numbers after the decimal? Double? Decimal?

 

[Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+]

Actually, it is set to "long integer." What should it be set to in order to
allow the display of actual numbers after the decimal? Double? Decimal?

Use the Currency data type. Currency stores decimals and is the
best type for working with money matters.

 

[John W. Vinson]

Why, even if I change the decimal place setting to two in the table, does
Access always round up or down to the nearest dollar in a number field set to
currency. Is there a way to set a field's display to currency but still
allowing the display to show the actual numbers after the decimal (Such as
$13.13 instead of having it round down to $13.00). Thanks.

Don't confuse the field's *datatype* with its *format*. Microsoft helps keep
you confused by providing both a Currency datatype (along with Text,
Date/Time, Number, Yes/No etc.) and also a Currency format. The latter can be
applied to a Number field - but it affects only how the number is displayed,
not what you can store there! The default Number datatype is a Long Integer,
and integers are by definition whole numbers.

Change the datatype of this field from Number to Currency and you'll get four
(no more, no fewer) decimals.

John W. Vinson [MVP]

 

[Guest]

Thank you.

Use the Currency data type. Currency stores decimals and is the
best type for working with money matters.

 

[Guest]

Thank you for your thorough and clear explanation.

 

[Jamie Collins]

Use the Currency data type.Currency stores decimals and is the
best type for working with money matters.

Your opinion! I prefer DECIMAL because I can choose not only the
precision (CURRENCY has a ridiculously large hard-coded precision of
19) and scale (CURRENCY's is hard-coded to be four) but also the
rounding algorithm (CURRENCY's is hard-coded to exhibit banker's
rounding) e.g. typically I want to be able to define scale one greater
in order to retain a significant figure with which to round to the
scale required (the DECIMAL type rounds by symmetric truncation).

That said, CURRENCY has better support in VBA and is a fixed decimal
numeric type (as is DECIMAL) and therefore makes a better choice than
than Double or Single which are floating point a.k.a. *approximate*
numeric types.

Jamie.

--

 

[Jeff Boyce]

So you are saying what matters is what use you need to make ... sounds like
using the tool designed for the job!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jamie Collins]

So you are saying what matters is what use you need to make ... sounds like
using the tool designed for the job!

I note that you and many of your fellow Access MVPs (plus other
regulars) seem to advocate using CURRENCY for non-monetary data: wrong
tool, wouldn't you agree? A typical example I see is a column for tax
*rate* which uses the CURRENCY data type -- how confusing is that?!

Consider that CURRENCY is not suitable for *all* monetary data e.g.
exchange rates are typically quoted to five decimal places. While it's
true I once implemented a spec that effectively read, "We want to use
the Microsoft proprietary behaviour; we haven't bothered to find out
what that is but if it's good enough for Microsoft then it's good
enough for us", but in my experience such specification is rare.

So, yes, for me more often than not CURRENCY is not an exact fit
therefore I'd 'roll my own' using a more standard data type. [Aside:
if portability is a consideration for you too, note that most SQL
DBMSs have a fixed point decimal type based either on SQL-92's DECIMAL
or NUMERIC types, as does Jet (IIRC Jet's DECIMAL most closely
resembles SQL-92's NUMERIC); the same cannot be said about proprietary
monetary types (which are nonexistent in the SQL-92 standard) e.g.
even SQL Server's MONEY has different inherent behaviour (rounding,
implicit casting, etc) from Jet's CURRENCY!]

Jamie.

--

 

[Jeff Boyce]

Jamie

Thanks for the empirical background/basis.

My comment was about using "opinion" as a basis for choosing, rather than
fitting a tool to a job.

Regards

Jeff Boyce
Microsoft Office/Access MVP

So you are saying what matters is what use you need to make ... sounds
like
using the tool designed for the job!

I note that you and many of your fellow Access MVPs (plus other
regulars) seem to advocate using CURRENCY for non-monetary data: wrong
tool, wouldn't you agree? A typical example I see is a column for tax
*rate* which uses the CURRENCY data type -- how confusing is that?!

Consider that CURRENCY is not suitable for *all* monetary data e.g.
exchange rates are typically quoted to five decimal places. While it's
true I once implemented a spec that effectively read, "We want to use
the Microsoft proprietary behaviour; we haven't bothered to find out
what that is but if it's good enough for Microsoft then it's good
enough for us", but in my experience such specification is rare.

So, yes, for me more often than not CURRENCY is not an exact fit
therefore I'd 'roll my own' using a more standard data type. [Aside:
if portability is a consideration for you too, note that most SQL
DBMSs have a fixed point decimal type based either on SQL-92's DECIMAL
or NUMERIC types, as does Jet (IIRC Jet's DECIMAL most closely
resembles SQL-92's NUMERIC); the same cannot be said about proprietary
monetary types (which are nonexistent in the SQL-92 standard) e.g.
even SQL Server's MONEY has different inherent behaviour (rounding,
implicit casting, etc) from Jet's CURRENCY!]

Jamie.