We help IT Professionals succeed at work.

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

peispud
peispud asked
on
502 Views
Last Modified: 2017-03-13
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

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.
peispudTech

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"?
peispudTech

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.
peispudTech

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:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Luke ChungPresident
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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 :)
Luke ChungPresident
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
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
Luke ChungPresident
CERTIFIED EXPERT

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

Author

Commented:
Thank you