Currency field that has too many digits after the decimal point.


In my database, the [Retail] field is set to Currency.

However, I have detected that  one entry has more than two decimal places.
Ie...  it actually is 25.0343  rather than 25..03

I would like to detect / correct only those records with this problem.

Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi peispud,

In your table design view, you have a few options available to address this issue.

Before making any suggestions could you please answer the following two questions?

1. Is the field you refer to a calculated field?
2. How many records reside in your table?

peispudAuthor Commented:
No. The field does not refer to a calculated field.

There are several thousand records.  Less than 10, 000
Ok, so in in the design view of your table, can you tell me what is the setting for "Decimal Places"?
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

peispudAuthor Commented:
It's set to currency.   I checked and it was set to auto
Ok, change the field "Decimal Places" setting from "Auto" to 2 and tell us if that fixes it.
peispudAuthor Commented:
I changed the "Decimal Places" setting to 2 but this did not resolve the problem.

I am attempting to transfer the database into another database via a csv file.

The new database is very stringent about the data,  so it will reject anything with more than two decimal places.,

I can deal with this by rounding before writing to the csv file, but I thought that there might be a better approach.
That makes sense.

Once the data is there, you can only really change the values with an update query.  Depending on where the "Retail" value actually comes from and what it is used for within your application, I would strongly advise against any modification of the current values as it will more than likely cause a lot of headaches for you in future.  This is especially true if the values are used for accounting processes.

That said, you could do as you suggested by using an update query and rounding the current values to two decimal places.  Personally, I would insert another field in the current table and retain the original value - just in case - but that's entirely up to you.

Let us know if this helps.
The currency data type is fixed at four decimal places.  When you set the decimal places, you are affecting only the number as it is to be displayed.  To resolve the problem, use a query that exports the data rather than exporting the table.  In the query, use the Format() function to format the amount to two decimal places.
Gustav BrockCIOCommented:
I can deal with this by rounding before writing to the csv file, but I thought that there might be a better approach.

You cannot, of course, both round and not round. So what did you have in mind?

Anyway, whenever you have 2+ decimals but need two decimals only, you have to round.
You can either:

  • Round the values in the table
  • Round the values when exporting

In both cases you will have to decide if you wish to perform up, down, or 4/5 rounding. For a full description of this, view this article and code:

Rounding values up, down, by 4/5, or to significant figures

You probably wish to proceed with 4/5 rounding. To change the values permanently in the table, use SQL like this (do make a backup first):

Update YourTable
Set [Retail] = CCur(Format([Retail], "0.00")

Open in new window

For the export, leaving the table as is, export using a query like this:

Select Field1, Field2, Field_etc, Format([Retail], "0.00") As Retail2
From YourTable
Order By SomeFieldToSortOn

Open in new window

In any case, as you do want two decimals only, go through your application and locate the code or query that generates the four-decimal values. Then correct this to round to two decimals only.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Luke ChungPresidentCommented:
You should use a Double rather than Currency for the field type. It's more important to suite the data correctly. You can always control how you display it, but you can't fix storing inaccurate data.
Gustav BrockCIOCommented:
Luke, how can you? Too little sleep?

Currency should always be used for storing amounts, no exceptions (except if you need more than four decimals, and even then you should consider scaling the values).

Double will in no way "suite the data" more correctly, and will potentially lead to incorrect data if you either add positivie and negative values or subtract values. Thus, a use of Double could not have prevented the issue the questioneer deals with; on the contrary, it could have made it even worse.

I can recommend a great article that explains the problem with single and double.  It's called "When Access Math doesn't Add Up" and it can be found on the FMSINC website :)
Luke ChungPresidentCommented:
Gustav: What do you mean?

If you need to store data with more than 4 decimal points, it violates the premise of a currency field. The data needs to be stored in a field that can save the data exactly, which is what a Double or Float field handles in this situation.

The database doesn't know the data represents money. It's just a number.
Gustav BrockCIOCommented:
Luke, the questioneer doesn't need more decimals than four but fewer.

Of course, the database cannot "know" the purpose of the field, but the developer does. That's why Currency was chosen.

It's black to me why you should mean the use of Double could either have prevented the current issue or play any part in curing it.
It's like you are commenting on a different question ...

Luke ChungPresidentCommented:
Sorry. My mistake for sending this the wrong way.

The way to address this would be to run a query on that field by multiplying it by 100 and subtracting the CLng() of that field * 100. Those with a non-zero value are the ones with extra decimal places.
peispudAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.