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

peispud
peispud used Ask the Experts™
on
Hi.

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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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?

Thanks.

Author

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

There are several thousand records.  Less than 10, 000

Commented:
Ok, so in in the design view of your table, can you tell me what is the setting for "Decimal Places"?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
It's set to currency.   I checked and it was set to auto

Commented:
Ok, change the field "Decimal Places" setting from "Auto" to 2 and tell us if that fixes it.

Author

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.
Commented:
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.
~Tala~
Distinguished Expert 2017

Commented:
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.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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.

/gustav
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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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.

/gustav
Distinguished Expert 2017

Commented:
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 :)
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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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 ...

/gustav
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.

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial