best practices.rounding

[Josh]

I understand why Acess rounds the way it does (at least somewhat), and "bankers
rounding", so I'm not asking the basic rounding question. (just one level up
from basic ;-)

I've done rounding two ways, either Currency for both actual currency values and
and Double Number values. I've also used MS's SymArith function. (included at
end of this post, just in case anyone wondered what the function was.)

I'm wondering what the 'best practice' way is. Do you simply use Currency for
Currency, and not round to 2 decimal places. If so, then someone adding figures
up and comparing could come up with something different, because doesn't Access
store 4 digit for currency, which could have a slightly different totaled amount
than someone adding with excel, for example.

I know some people used to say to just use currency for numbers that are not
actual currency, and just format the Form or Report to not show the $ sign. That
works, but wouldn't the SymArith function be better?

Like I said, I'm just wondering what the best practice is. The few DB's I've
done work just fine, just wondering what the pros do.

Thanks, Josh

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function

 

[Jeff Boyce]

Josh

Define "best" ... "best" for obtaining what kind of result?

?"Best" as in "most accurate" (according to which rounding rule(s))?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Josh]

I suppose I should have used "Method most commonly used by the Pros" instead of
"best practices" because you're correct, "best" is subjective.

I guess I'm wondering about:
When using a Field whose Data Type is Number (double), whether to make it
Currency instead of Number, or to leave it as Data Type Number (double) and use
SymArith (or similar function) to further round it (Excel - type round).

I want to make sure (as far as possible) that the data that is actually stored
in the table (not formatted in a form or report) is "rounded" to the decimal
place I want for that particular database field. I'm trying to standardize on a
particular method as far as possible in order to reduce problems arising from
things like:
Person gets a long statement, adds it up, gets slightly different total because
the figures displayed are formatted, whereas the total summed is formatted also,
but on an overall format rather than a line by line format (if that makes
sense).
And, if exporting, so that the data doesn't have long decimal figures.

I realize no one solution fits everything, but want to standardize, then make
adjustments if needed.

Thanks.

 

[Guest]

Adding or subtracting individual base values will not result in any
discrepancy with manual addition or subtraction when a Currency data type is
employed. Where differences can arise is when computations involving
multiplication or division are done on those values.

Say you have a list of items supplied to a customer to whom you give a
discount. Doing this manually many people would calculate the net price per
item rounded to two decimal places, then add the rounded values to give the
total net price. When Access computes the net values for each item supplied,
however, it rounds the result more precisely to four decimal places. The
total net price produced from the summation of these values might differ from
the manually calculated total net price. The total net price produced by
Access is in most circumstances the more correct one as, by virtue of the
greater precision at the individual transaction level the cumulative rounding
errors which result from manually rounding to two decimal places at each
transaction level are suppressed, which is exactly what the Currency data
type is designed to do.

To many people who have been familiar with the manual approach of rounding
per transaction the result produced by Access seems erroneous as the total
can differ from the sum of the individually rounded amounts. Neither can be
said to be right or wrong, however; it all depends on how its wished to be
done in particular business models. Many small businesses prefer computed
values which mimic the manually calculated ones. It should be noted,
however, that when dealing in large currency amounts the result of cumulative
rounding errors can be significant in terms of monies lost or gained. This
is why rounding methods aimed at balancing out the errors by rounding up or
down in equal proportions were commonly used, the basis of this being that
over a large number of transactions the proportion in each case should
approach 50 per cent, but this does not in itself allow for large variations
in the individual amounts; if the transactions rounded up are mostly large
amounts and the ones rounded down are mostly small amounts there will be a
significant cumulative error even if exactly half round up and half round
down. Again the assumption was that over a large number of transactions the
this would balance itself out and any cumulative error would be insignificant.

For an overview of the many different rounding algorithms see:


http://www.pldesignline.com/howto/showArticle.jhtml?articleID=175801189


Ken Sheridan
Stafford, England

 

[Guest]

It occurred to me that one of my statements might have been a little
confusing. When I referred to the distortion which might result from an
imbalance in the proportions of high and low individual amounts this applies
when one or other falls one side of the significant digit to which a value is
rounded. This arises with 'headline' rounding to a significant digit to the
left of the decimal point rather than rounding to the nearest cent. As an
example the authority I used to work for had an annual budget of around 500
million GBP, which is of course a rounding to the nearest 100 million of the
actual budget amount. Around half of that figure was one department's
budget. As most departments' budgets were less than 100 million GBP,
however, if the rounding had been done at departmental budget level, most
would have been rounded up and only a couple rounded down. The sum of these
would then have been grossly higher than 500 million. This is an extreme
example of course and no-one in their right mind would do it that way, but it
serves to illustrate the principle.

Ken Sheridan
Stafford, England

 

[Josh]

Visited the link you provided, interesting, but way over my head. <g>

I'm just talking about generalties in regarding small business (a small mom &
pop-type store). Just curious as to whether the pro's "generally" use currency
for non-currency situations, when the decimal result required will never be more
than four decimal places, and usuall one or two decimal places.

And, if using Currency, whether for actual currency or non-currency situations,
whether the pros "generally" use a round function every step of the way or not.
(not proper syntax, just an example):
MyRound(field1*field2) / MyRound(field3*field4) rather than
MyRound((field1*field2)/(field3*field4)

I know this has been talked to death over the years, but what the heck, I've
always wondered, so thought I'd ask.

Thanks

 

[Guest]

Its quite usual to use the Currency data type for non-currency data. Its
common in many situations to format numbers to two decimal places as this
provides a suitable display precision for many purposes. This is of course
exactly analogous to the display of currency values (unless you are a
currency dealer in which case you'd work to a much higher precision) so it’s
a convenient way of suppressing the cumulative rounding errors which might
otherwise result.

As regards the second point, as I said in my first post, there is no
absolute answer to this. Taking our discounted customers as an example
again, if you round the net price at each transaction step the total net
price for a number of transactions might well differ from the net price if
the rounding were done on the sum of the (unseen) non-rounded values, but is
a more accurate application of the discount ratio to the total gross price.
So neither the vendor nor buyer gain or lose. If each transaction's net
price is rounded and the results summed to give the total net price then the
total net price will in all probability not correctly reflect the discount
applied to the total gross price. So one party gains, the other loses out.
Regardless of this many people feel more comfortable with this as the total
net price at the foot of an invoice will accurately represent the sum of the
individual net prices shown on the invoice. If the discount applies
uniformly to all transactions there is an easy answer to this though, which
is not to expose the individual net prices, but to show only the individual
gross prices and apply the discount to the total. However, if the level of
discount varies between individual transactions, based on the quantity
ordered or the type of product say, then the options are (1) show the
individual net prices and sum them to give the total price, accepting that
the total net price may differ from the sum of the individual net prices as
seen. (2) show the individual net prices and sum the rounded values of them
to give the total net price (3) group the transactions by the discount rate
and apply the discount and round the net price at each group level, summing
the rounded values to give the total net price, or (4) group the transactions
by the discount rate and apply the discount at each group level without
rounding, accepting that the total net price may differ from the sum of the
grouped net prices as seen.

With financial data the choice as to which level to round really rests with
the operator of the business, and the programmer should take the lead from
the client after appraising them of the options available, but with other
types of data the choice is more clear cut. Say you have a series of
measurements, to a precision of 4 decimal places of whatever unit of
measurement you are using, between 10 points along a road. To round each
measurement and sum them to get the distance from point 1 to point 10 would
clearly be wrong and rounding should be done once, on the sum of each
individual measurement, and I would say that this should be adopted as a
general principle in all situations unless there are good business reasons
for rounding at an earlier stage. With currency data the maintenance of good
customer relations may well be a perfectly valid business reason for opting
to round at each transaction level; many people find it hard to grasp that
there can be a money value smaller than the penny in their pocket, and if the
total at the bottom of a list of monetary values differs from the sum of what
they can actually see on the page believe they have been short-changed.

Ken Sheridan
Stafford, England

 

[Josh]

Thanks!
Josh

Its quite usual to use the Currency data type for non-currency data. Its
common in many situations to format numbers to two decimal places as this
provides a suitable display precision for many purposes. This is of course
exactly analogous to the display of currency values (unless you are a
currency dealer in which case you'd work to a much higher precision) so it’s
a convenient way of suppressing the cumulative rounding errors which might
otherwise result.

As regards the second point, as I said in my first post, there is no
absolute answer to this. Taking our discounted customers as an example
again, if you round the net price at each transaction step the total net
price for a number of transactions might well differ from the net price if
the rounding were done on the sum of the (unseen) non-rounded values, but is
a more accurate application of the discount ratio to the total gross price.
So neither the vendor nor buyer gain or lose. If each transaction's net
price is rounded and the results summed to give the total net price then the
total net price will in all probability not correctly reflect the discount
applied to the total gross price. So one party gains, the other loses out.
Regardless of this many people feel more comfortable with this as the total
net price at the foot of an invoice will accurately represent the sum of the
individual net prices shown on the invoice. If the discount applies
uniformly to all transactions there is an easy answer to this though, which
is not to expose the individual net prices, but to show only the individual
gross prices and apply the discount to the total. However, if the level of
discount varies between individual transactions, based on the quantity
ordered or the type of product say, then the options are (1) show the
individual net prices and sum them to give the total price, accepting that
the total net price may differ from the sum of the individual net prices as
seen. (2) show the individual net prices and sum the rounded values of them
to give the total net price (3) group the transactions by the discount rate
and apply the discount and round the net price at each group level, summing
the rounded values to give the total net price, or (4) group the transactions
by the discount rate and apply the discount at each group level without
rounding, accepting that the total net price may differ from the sum of the
grouped net prices as seen.

With financial data the choice as to which level to round really rests with
the operator of the business, and the programmer should take the lead from
the client after appraising them of the options available, but with other
types of data the choice is more clear cut. Say you have a series of
measurements, to a precision of 4 decimal places of whatever unit of
measurement you are using, between 10 points along a road. To round each
measurement and sum them to get the distance from point 1 to point 10 would
clearly be wrong and rounding should be done once, on the sum of each
individual measurement, and I would say that this should be adopted as a
general principle in all situations unless there are good business reasons
for rounding at an earlier stage. With currency data the maintenance of good
customer relations may well be a perfectly valid business reason for opting
to round at each transaction level; many people find it hard to grasp that
there can be a money value smaller than the penny in their pocket, and if the
total at the bottom of a list of monetary values differs from the sum of what
they can actually see on the page believe they have been short-changed.

Ken Sheridan
Stafford, England

 

[Albert D.Kallal]

You really just have to use currency.

You can choose to force the result to 2 decimal places (round to 2), but
REGARDLESS, YOU MUST use
currency data type. YOU CAN NOT USE double.

eg:

Public Sub TestAdd()
Dim MyNumber As Single
Dim i As Integer

For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub

Here is the actual outpput of the above:
1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1


You can see that after just 7 addtions..already rounding is occuring

and if we add the follwing line of code to the end of the above:

if MyNumber = 10.1 = True then
msgbox "the number is 10.1"
else
msgbox "the number is somthing else"
endif

The above will actuall produce:

the number is something else

So, regardless of what links, what concepts. If you are writing business
applications in Excel, or ms-access, you MUST USE currency data type. Using
double vars can still result in rounding errors. I would not trust that
rounding code you have.

So, has this issue been done to death? Hum, not really, but the simple
answer is that you must use currency data types. If you don't want values
beyond 2 decimal places, then build a function that truncates to 2 decimal
places, and always store that value. But, the rule still remains that you
have to use currency data type, and can NOT use double type if you don't
want any rounding errors.

 

[Josh]

Ok, sounds good, to use currency wherever Integer won't work.

What round function would you suggest, to always round to 1 or 2 decimal, NOT
using Banker's rounding. So the result could be compared to Excel. I've
only made a few DB programs for a couple of 'very' small business's, and they do
expect the results to match excel.

Looked around your website very briefly, picked up an idea or two for user
interface.

Thanks.

 

[Albert D.Kallal]

What round function would you suggest, to always round to 1 or 2 decimal,

NOT
using Banker's rounding. So the result could be compared to Excel

If you use currency, then for the MOST part, you WILL get the same results
of Excel.

In some cases, such as tax calculations, or whatever, you simply scale the
result.

Lets assume we need to take 17.5% of a given amount of $19.23

A normal approach to calculate the tax on the above would be

$19.23 * 0.175 = 3.36525

In the above, we see 5 digits. The simple solution I use is to "scale" the
results. Thus, you use 17.5. You get

$19.23 * 0.175 = 336.525

However, after we are done using the calculation, we simply drop the
fraction part, and down -scale


result = int($336.525) / 100
3.36

hence: ccur (int($19.23 * 17.5) / 100)

For the most part, you do NOT need a general rounding routine, since MOST
numbers are existing prices, and thus rounding is not a problem.

It is ONLY going to be needed a FEW cases of calculation. And, really, why
not just allow up to 4 digits, as that should result in the SAME as Excel
anyway. So, really, if you just used currency, and allowed some things to go
to 4 decimal places, you don't have to worry.

 

[onedaywhen]

You really just have to use currency.

You can choose to force the result to 2 decimal places (round to 2), but
REGARDLESS, YOU MUST use
currency data type.

The OP wants two decimal places then why not use the DECIMAL data type,
which can be explicitly scaled to two decimal places?

Are you aware that when (down thread) you use 0.175 and 1.75 in SQL
code you are using DECIMAL values? Example:

SELECT 0.175, TYPENAME(0.175) FROM AnyTable;

Are you aware that arithmetic operations on a value using a DEICMAL
value will result in a a DECIMAL? Example:

SELECT CCUR(19.35) / 1.175,
TYPENAME(CCUR(19.35) / 1.175) FROM AnyTable;

In your examples, are you suggesting the OP also stores the tax rate as
CURRENCY? That will affect the type and value of the results. Example:

SELECT DISTINCT CCUR(19.23) * CCUR(0.175),
TYPENAME(CCUR(19.23) * CCUR(0.175)) FROM AnyTable
UNION ALL
SELECT DISTINCT CCUR(19.23) * 0.175,
TYPENAME(CCUR(19.23) * 0.175) FROM AnyTable

You demonstrated how seven iterations could affect the accuracy of a
calculation using FLOAT (Double) values. Have you considered that
storing values as CURRENCY rather than to two decimal places, will have
a similar effect? Consider this example:

CREATE TABLE DropMe (
dec_col DECIMAL(19, 2) NOT NULL,
cur_col CURRENCY NOT NULL,
float_col FLOAT NOT NULL
)
;
INSERT INTO DropMe (dec_col, cur_col, float_col)
VALUES (19.23, 19.23, 19.23)
;

Note I am inserting DECIMAL values into all the columns but the value
is unaffected here.

Now execute this seven times:

UPDATE DropMe
SET dec_col = dec_col * 1.175,
cur_col = cur_col * 1.175,
float_col = float_col * 1.175;

After seven 'iterations' I get

decimal col = 59.41
currency col = 59.4629
float col = 59.4626623932098

Note I used DECIMAL values for the tax rate. If I use a CURRENCY value
i.e.

cur_col * CCUR(1.175)

then the result is 59.4625, only marginally closer to the expected
results using DECIMAL(p, 2).

Jamie.

--

 

[Albert D.Kallal]

Are you aware that when (down thread) you use 0.175 and 1.75 in SQL
code you are using DECIMAL values? Example:

SELECT 0.175, TYPENAME(0.175) FROM AnyTable;

That assumes we are using the decimal data type, and for the most part, in
ms-access, we use the currency type.

In your examples, are you suggesting the OP also stores the tax rate as
CURRENCY?

Yes, I am suggesting that.

That will affect the type and value of the results. Example:

Yes, and so will using decimal type also. What was your point?

You demonstrated how seven iterations could affect the accuracy of a
calculation using FLOAT (Double) values. Have you considered that
storing values as CURRENCY rather than to two decimal places, will have
a similar effect? Consider this example:

Yes. And that is why I said that AFTER you make a calculation, you de-scale
the value BEFORE YOU STORE it back into the database...

Now execute this seven times:

Ah. but my advice was that for EACH calculation, you do the scaling and
truncation. You are not doing that in the above!!! Further, you have
fraction values in your calculations that IS MORE THEN 2 decimal places!!
(what gives!!!).

So, I am mostly assuming here that the math/calculations is going to occur
in VB code, and not using sql expressions. This tends to be the case for
*MOST* applications. So, for the most part, payroll tax, and MOST
calculations in ms-access is NOT going to occur in sql expressions, but in
VBA code that is built into a form. And, for those times that you do using
sql expressions, if you stick to 2 decimal places (which you example did
not), then again with currency (or long integers), you will not have any
problems.

Note I used DECIMAL values for the tax rate. If I use a CURRENCY value

The problem is that VBA and ms-access does NOT have support built in for
Decimal types. In fact, I would go so far as to suggest that you avoid using
decimal types altogether when writing ms-access and a JET based database.

So, in effect, just about no one I know uses decimal type data fields in
ms-access. In fact, YOU CAN NOT EVEN CREATE a decimal field type with the
ms-access interface when using a JET database.

So, while your points on using decimal types is appreciated, you seem to
have forgotten that no one in ms-access land uses the decimal data type with
JET file shares in the first place.

In addition to no direct support in VBA for decimal data types, you also
have a number of problems during exports, and even sorting of decimal types
in ms-access. A few of the problems are outlined here:

http://allenbrowne.com/bug-08.html

I can't in any good faith recommend the use of decimal data type in access
based applications.

 

[onedaywhen]

That assumes we are using the decimal data type, and for the most part, in
ms-access, we use the currency type.

This all sounds a bit strange to me.

'We use CURRENCY in ms-access land because we use CURRENCY."

'We don't use DECIMAL because we use CURRENCY.'

'When asked to avoid bankers rounding we use CURRENCY with inherent
bankers rounding.'

'We define tax rates as CURRENCY in the schema because it looks nice in
the interface.'

I can't in any good faith recommend the use of decimal data type in
access based applications.

The OP asks for 'decimal, two decimal places, no bankers rounding' yet
you say they *must* use CURRENCY with four decimal places and bankers
rounding...?

The problem is that VBA and ms-access does NOT have support built
in for Decimal types.

VBA6 has a Decimal variant subtype and a CDec() casting function. I
understand Decimal was implemented based on scaled integers i.e. it was
simplest to implement as a reference type rather than a value type
because the integer parts need not be in contiguous memory addresses.
Are there any practical consequences of Decimal not being an intrinsic
type? Before you say that variants are inefficient, consider that fixed
point types lack the *hardware* support enjoyed by integers and
floating point.

And who says a native Jet data type must be mirrored by an intrinsic
VBA type? CHAR, VARCHAR, MEMO, OLEOBJECT, MEMO and BINARY are distinct
Jet data types but they all map to the VBA String intrinsic type, being
an 'inefficient' reference type. TINYINT maps to Long but SMALLINT maps
to Integer.

If you are saying that it requires a VBA intrinsic value type for a Jet
4.0 to be adopted in ms-access land, what about NCHAR(n)? It maps to
VBA's String * n type but I don't see either used too much.

Jet 4.0 uses the DECIMAL type natively, as I have demonstrated.

'ms-access' means many things to many people. The only version I have
installed right now is Access 12 beta 1. When I create a new table and
select 'Number' for data type I am by default given field size =
Decimal, with options for Precision and Scale. I'm not sure what this
means but it certainly looks like built in support for Decimal to me.

you also
have a number of problems during exports, and even sorting of decimal types
in ms-access. A few of the problems are outlined here:

http://allenbrowne.com/bug-08.html

As for Allen Browne's old chestnuts (yawn):

The 'Invalid Argument Error When You Export Data to a Text File'
problem can be worked around using a different (better) export approach
e.g. embedding an odbc connection a query:

SELECT decimal_col
INTO [TEXT;DATABASE=C:\;].[MyExport#txt]
FROM TestDecimal;

This method has an advantage in it can automatically create a
schema.ini file identifying the data type correctly e.g.

Col1=decimal_col Decimal Precision 17 Scale 2

The 'sort order' bug only affects limited scenarios (e.g. must include
negative values AND the sort order must be descending AND the sorting
must be done in the engine), the results are predicable rather than
'wildly inaccurate' as claimed and the workaround is simple and
reasonable (e.g. do the sorting in the recordset) and possibly
beneficial (e.g. avoiding proprietary SQL constructs, client side
sorting more efficient than server side, etc).

The lack of an explicit casting function in Jet is not a blocking issue
because Jet natively sees 'decimal' values as DECIMAL in nature.

Have I missed anything?

I conclude:
- the VBA type issue etc is a bit of a red herring;
- there is built in support for Decimal ms-access, though there are
limitations e.g. ANSI query mode;
- people frequently use DECIMAL values in SQL code, thereby coercing
other values to DECIMAL, without realizing it and with no ill effect.

I rather suspect this has something to do intangibles such as long term
ms-access users having issues with the fact DECIMAL is not support in
DAO, with the other issues (sort order, VBA types, etc) used because
they are more tangible, yet when you look at them you realize they are
not terribly significant issues.

One last thing that is puzzling me, Albert. You've convinced us that
double float is unacceptable, so what do you recommend when the client
requires five decimal places? Multiply by 10 and store as CURRENCY?

Jamie.

--

 

[Albert D.Kallal]

This all sounds a bit strange to me.

It might sound strange, but it is the norm!! It is what MOST developers in
ms-access use. If you can find evidence to counter this fact, then I am all
ears. The only reason why it sounds strange to you is because you have not
experienced with the environment. The NORM is to use currency.

The OP asks for 'decimal, two decimal places, no bankers rounding' yet
you say they *must* use CURRENCY with four decimal places and bankers
rounding...?

Well, ok then "must" use is a harsh word. Geesh!! Sure, lets not get in to a
fight on semantics. Lets just change "must use" to it is a good idea, and
reasonable intelligently access developers use currency. After all, we are
trying to be practical here. Don't you want to be practical here?

To clarify the use of "must" was in comparison to floating types (eg: you
must use a integer type - even scaled decimals, or currency, or
whatever..but MUST NOT use floating types, or you WILL get rounding) -- but,
really..not a big deal.

Are there any practical consequences of Decimal not being an intrinsic
type?

Yes, there are!!

Why have any data typing at all? You mean that the whole computing industry
should throw out data typing? Wow, that is a amazing point on your part.
Cleary, you are not suggesting that. However, you ARE asking why have data
typing? If I have to stand here and explain why data typing is useful, then
you have a problem. Using a intrinsic (supported) data type is FAR
preferable WHEN it makes sense to do so! Now you come along and ask why
should we using data types that are supported by a given development
language? You mean you don't think there is any advantage to using a data
type that is supported by given language?

When appropriate, data typing is good. We have data typing so our compiler
and code can more specially define the problem at hand. I mean, you can
often use one data type in place of another, but WHY NOT USE a good defined
and appropriate data type for the problem at hand? This is all about WHAT IS
A GOOD RECOMMENDATION. We are not having discussion about what is POSSIBLE,
but we are MOST certainly talking about what is a good recommended practice
by REASONABLE people!

Before you say that variants are inefficient, consider that fixed
point types lack the *hardware* support enjoyed by integers and
floating point.

I did not make the claim that they are inefficient. My point is that it is
PREFERRED to use a data type that can be defined in code. Really, not much
more to say, unless you don't believe in data typing at all? What is your
point here? (my point is that data typing can be a help WHEN it is
appropriate, nothing more, nothing less). Development platforms have data
typing because it is usually a good idea. What are you disagreeing with
here?

And who says a native Jet data type must be mirrored by an intrinsic
VBA type?

Again,. who said that? Again, MUST is a harsh word. Why do you have to
polarize this issue this way? A good choice, or a choice by a reasonable
intelligent person is most certainly my recommending here.

If you are saying that it requires a VBA intrinsic value type for a Jet
4.0 to be adopted in ms-access land, what about NCHAR(n)? It maps to
VBA's String * n type but I don't see either used too much.

No, not at alll. I am not saying must. However, I am certanly saying it is
nice to be able to work with a supported data type.

'ms-access' means many things to many people. The only version I have
installed right now is Access 12 beta 1. When I create a new table and
select 'Number' for data type I am by default given field size =
Decimal, with options for Precision and Scale. I'm not sure what this
means but it certainly looks like built in support for Decimal to me.

There may be support in the new version ms-access, but that is not the case
RIGHT NOW. You can't give "general" accepted advice to ms-access developer
community based on a product that IS NOT EVEN OUT!! This is no a big issue
here, but we are just trying to give reasonable advice.

If over time support for decimal data types improves, then I have ZERO
problems accepting the use of decimal data over currency types in ms-access.
As it stands right now, using decimal types is NOT normally done. What more
needs to be said? Don't ask me, go ask the community at large and see what
feedback you get...

Look, you *can* use decimal data types. However, for MOST CASES you are
better off to use currency. You have NOT made the case that using decimal
data types for storing data is a better choice then that of currency GIVEN
THE CURRENT develpoment landscape called ms-access.

As for Allen Browne's old chestnuts (yawn):

The 'Invalid Argument Error When You Export Data to a Text File'
problem can be worked around using a different (better) export approach
e.g. embedding an odbc connection a query:

SELECT decimal_col
INTO [TEXT;DATABASE=C:\;].[MyExport#txt]
FROM TestDecimal;

The above is still a work around. Is still something that end users would
have to be aware of. And WORSE, users can not use the standard interface
tools. If you are telling me there is workarounds...then fine. I just
telling you that you have some problems with the standard interface, and you
can avoid those problems by avoiding the use of decimal types.

the results are predicable rather than
'wildly inaccurate' as claimed and the workaround is simple and
reasonable (e.g. do the sorting in the recordset)

Sorting in a reocrdset is of NO use when running a reprot. It is still a
UN-EXPECTED got-ya that is likey to bite any user of the product.

and possibly
beneficial (e.g. avoiding proprietary SQL constructs, client side
sorting more efficient than server side, etc).

Well, lets not get too funny here!! We don't have a client to server setup,
we are talking about a JET based system, so it is ALWAYS client side!!

Have I missed anything?

Yes, what you missed is that devleoper community as a whole does not agree
with you...

I conclude:
- the VBA type issue etc is a bit of a red herring;

Well, it certainly is NOT a huge deal!! However, it is NICE to use something
that is supported. It is not a huge deal, or the end of the world. However,
we are talking about *reasonable* choices here.

One last thing that is puzzling me, Albert. You've convinced us that
double float is unacceptable, so what do you recommend when the client
requires five decimal places? Multiply by 10 and store as CURRENCY?

If you NEED more then what currency offers, then you have to make a design
decision. However, in THE ABOVE EXAMPLE we DID NOT NEED more then what
currency officers.

And, if we did need more then 4 decimal places, then sure, the advantages of
decimal type would come into play. That is no different then asking what do
we do when we need to use a string!! Well, then use a string!! Again, why is
that such a problem with you? If you want to re-define the problem, and them
come back to me and tell me that the advice given changes? Well, ggessh...no
kidding Sherlock!! I am at a complete and utter loss here as to why you
would not think re-defining the problem would case my advice not to change?

And, as for scaling by 10, or 100? Well, IBM's U2, jBase, Rainging Data
(pick) and Advanced Revelation all in fact does EXACTLY THAT!!! So, yes,
that is a possible, and when you look at industry vendors (IBM, jBase, pick,
AREV), that is exactly what they do!

What did you do in development platforms when you don't have scaled decimal
numbers? Having written payroll applications from scratch in Pascal, I can
assure you that I am well versed on what works, and what does not. It would
seem that IBM, jBase, Pick, AREV all agree with my approach. I am not
exactly alone in this view here!! When you write on these systems, your code
has to take into account that values are normally stored as strings, and
with NO decimal places. (your code has to assume the data is scaled).

But, again, if ones needs more then 4 decimal places, then using something
that allows more then 4 decimal places makes sense! (what else would one
conclude here? ). Why is that a problem or a issue? You seem to be taking
greats lengths to polarize my advice. My advice applies to the original
posters question. I don't think using decimal data type is a requirement
here, nor is a advantage, and nor is it supported "well".

It is not question of "must", but what is a reasonable decision for a given
situation and information at hand. If that situation changes, then obviously
the advice given will change also. Why would it not?.

I just don't see the problem here at all....

 

[Albert D.Kallal]

And, just to clarify, the decimal data has been in ms-access since jet 4

(so, you can well use them -- and, the table defining part of access DOES
have decimal data types --- I believe all the way back to a2000 -- so, your
ability to use/define decimal data types is NOT a a2007 feature, but
certainly goes back a few versions).

So, decimal data types are useable. And, if the problem at hand needs only 2
decimal places, I don't think your suggestion is out of the question by any
stretch. For most financial stuff, currency is preferred, but, I can and do
accept that decimal is a viable option here. I don't want to come across to
harsh here. In reading my post, I do come down a bit hard, and that was not
my intention here. My apologies.

Last, but not least...the decimal feature is available, and is worthy of
considering. There is several approaches here to skin this cat, and yours is
a fair suggestion. I hope I not thrown any discouragement on your efforts to
give advice.

Note careful that you WERE GIVING FREE advice. You were trying to help! At
the end of the day, that is the type of people we want to see here, and thus
I do tip my hat to you. I hope in any way I not discouraged you, and your
point you were freely giving here....

Sincerely,
Albert D. Kallal

 

[Josh]

I appreciate the advice, and the later postings between you and 'onedaywhen'....

I have to redo an older DB, and was thinking of converting all double data types
to currency since I have no need for greater than 4 decimal places.
(not changing existing data, but rather 'archive old data and start fresh')

Thanks!

 

[onedaywhen]

Thanks, Albert, no worries. I appreciate all your feedback.

I'll resist the temptation to reply to the detail because I don't want
to obscure the final outcome i.e. that the DECIMAL data type is worthy
of consideration.

Jamie.

--