The Access help on currency fields states:
Use a Currency field to prevent rounding off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. A Currency field occupies 8 bytes of disk space.
Excellent you figure. Now if your code and queries are doing any multiplying or dividing you just have to remember to round to two decimal places.
And you're done. Not so fast. (Otherwise what's the point of this blog entry.)
Turns out the users can accidentally enter a tenth of a cent and/or a hundredth of a cent. And you won't notice it unless the focus is on the field. And you won't notice unless someone points out a weird rounding problem.
One of my clients MDBs had three of those fields. Worse, one of them was on a parts price and thus would've continued to propagate for years. Another client had the $0.001 on a rate table and it had been transferred to 500 of the 600K records on the table.
There is a KB article on this topic ACC2000: Round or Truncate Currency Values to the Intended Number of Decimals which shows how to put some simple code in the AfterUpdate event of a currency control.
To find those wonky data in the tables use the following query:
And you're done, right? Wrong.
Dates can have a similar problem when the century portion is hidden by the default Windows date formatting. You will find this when attempting to upsize the Access database to SQL Server as the SQL Server smalldatetime field has an epoch date of 1900-01-01. In a table of several hundred thousand records we found 12 of these records where the value was before 1900. The dates weren't actually used in any computations which is why no one had noticed them.
Whoops. Most North American developers would think in terms of pennies so rounding to 2 decimal places is great. But not so the folks in other countries.
So I created a constant on my hidden Global Options form and placed the value of 2 in it. I referenced that form variable in all my code and queries so I'm set. If I ever sell my software in another country I'm set to do some more investigating on the topic of how to fetch the currency decimal places from the users regional settings and go from there.
(Note that a potential problem could exist if the users had their systems formatted in different currencies or different decimal places. Which is why I'm ignoring this end of the topic for now.)
Currency formatting display.
Finally when I did some searching on this topic I came across several pages on a related topic again of interest to folks whose applications need to support multiple currencies.
Using the Currency field data type - without the hassle
Microsoft Access and the Ten-Year-Old Currency Bug