decimal display

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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?
 
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?
Click to expand...

Use the Currency data type. Currency stores decimals and is the
best type for working with money matters.
 
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.
Click to expand...

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]
 
Use the Currency data type.Currency stores decimals and is the
best type for working with money matters.
Click to expand...

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.

--
 
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
 
So you are saying what matters is what use you need to make ... sounds like
using the tool designed for the job!
Click to expand...

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.

--
 
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

Jamie Collins said:
So you are saying what matters is what use you need to make ... sounds
like
using the tool designed for the job!
Click to expand...

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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Preventing rounding numbers to nearest dollar 2
Access 2003 - Currency format problems 1
Stop Rounding Currency 2
Set a number field with decimal places. 3
Decimal places in report 2
Access keeps rounding down... Decimal 5
Displaying digits after the decimal point 5
Currency Decimals 5

Back
Top