Accounts/Bookkeeping data type

[Ian Chappel]

I am setting up a fairly complicated accounts system, for day-to-day data
entry etc.

Given that I never want to deal in amounts less than pennies/cents, is it
better to store all values in pennnies/cents (i.e. integers), and x100 in
forms and reports? This would get round (excuse the pun) any rounding
problems. Currency data type seems to store values more accurately than I
need, which could create problems.

 

[Craig Alexander Morrison]

Personally I would continue to use the currency datatype and set the
AfterUpdate event of any input field thus:

Me![Amount1] = Format(Me![Amount1], "##,##0.00")

This will strip any decimal places after the first two, it will round if the
third decimal place is 5 or greater but at least the user will see the value
appear to two decimal places and that will be all that is actually stored in
the field. I believe several existing products use this method, I know that
MS Money does, not that that should be the clincher.

The optional "," in the format string shows the thousands placeholder, which
is much easier to read.

It is best to define the amount fields in the tables as Data Type: Currency,
Format: Standard, Decimal Places: 2 this will make form building a bit less
fiddly with the correct display format being set up automatically from the
Table definition of the fields.

The reason the Currency field is best retained is that it is more accurate
especially if you will need to cater for interest rates and exchange rates
which all go to 4 decimal places.

To quote MSA2003 Help "Microsoft Access provides two field data types to
store data containing numeric values: Number and Currency. Use a Number
field to store numeric data to be used for mathematical calculations, except
calculations that involve money or that require a high degree of accuracy."

 

[Ian Chappel]

Thanks Craig

You're probably right!

I'm never going to want to store currency amounts to more than 2 DP - it
does seem unfortunate that tbe Currency data type does not have the ability
to ensure that only a certain number of DP's are stored - I would have
thought a very common requirement. I guess I have the choice of rounding on
input as you suggest, or /100 on input and x100 to display. One operation
must be easier than two - it just worries me that for example after
totalling a large number of figures that rounding errors may occur - i.e. I
may be an odd penny/cent adrift, which could not occur with integers.

 

[Craig Alexander Morrison]

Ian you can apply the format statement in an update query to ensure all
records are correctly formatted/recorded before running reports, or if you
do not want to touch the records you can use the format statement in the
query you are running to get the totals.

Indeed you could skip updating the value on entry if that was not important
and just do the bulk update in a query prior to using the data for reporting
and other purposes. Perhaps do both for a belts and braces approach.

As most of our systems have been multi-currency we have used the Currency
field as many of our values are the result of a currency exchange rate but
you are right the rounding can bite you if you aren't careful. It can look
quite wonderful to see a computer produce a report showing thousands of
lines with zero pence and a total of 2,345,345.04.