The decimal places setting is only for display. For example, (if you
remember elementary math stuff), a integer can not have a fractional value,
or any decimals. So, you can create a integer field in ms-access, and set
the number of decimal places to whatever you want. Of course, when you try
and enter decimal places, none will be saved.
It turns out that currency values only allow 4 decimal places, so, any
setting after 4 will be ignored.
The solution?
Hum, you could scale your numbers by 5 places.
so
123456
Would actually mean 1.23456
In fact, for many business programming environments such as the Data-BASIC
programming language for IBM'S U2 system, or even Pascal, you often use
packed decimal numbers which are in fact integers, and you SCALE the
results. In fact, in ms-access, the currency type is a integer value, and is
scaled FOR YOU by 4 places. You could use a double data type, and that
allows up to 13 significant digits. So, you can easily get 5 significantly
decimal places.
However, if these numbers are to be used for financial calculations, then
you better stick to using currency data types, as computers CAN NOT
represent fractions with any degree of accuracy. We can look at a expression
of 1/10. However, when you represent this as a fraction in a computer, the
value is only approximate, and not exact. For example, paste the following
code into a standard module. Put your cursor in the code and hit f5 to run
Public Sub test77()
Dim SomeNum As Single
Dim i As Integer
For i = 1 To 10
SomeNum = SomeNum + 0.1
Debug.Print SomeNum
Next i
Debug.Print "number is = to 1 " & (SomeNum = 1)
End Sub
The output of the above code to the immediate window is:
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8000001
0.9000001
1
number is = to 1 False
You can see that only after 7 additions, rounding errors are occurring. And,
when we test if the number is = 1, the result is false. So, you do need to
be aware that numbers in computers are approximate. You can change the
above number to a double precision number. At his point, we got so many
significant digits, that when you run the above code, you get:
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9
1
number is = to 1 False
Notice how we don't see the rounding anymore, but you can clearly see that
the resulting number is NOT = 1. So, you can often get away using a double,
as the rounding errors are not see as much. So, as a developer, or person
using a software development tool like ms-access, you need to learn these
concepts VERY quick (or, give up, and leave this software stuff to the
professionals).
So, in your case, if your number is for financial calculations, then I would
scale the currency data type by 1000 (that means you take all the values to
calculate, and scale by 1000. Then to display numbers, you de-scale by 1000.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
