Link to home
Create AccountLog in
Avatar of peispud
peispudFlag for Canada

asked on

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

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.
Avatar of Tusitala
Tusitala

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.
Avatar of peispud

ASKER

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"?
Avatar of peispud

ASKER

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.
Avatar of peispud

ASKER

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.
SOLUTION
Avatar of Tusitala
Tusitala

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of PatHartman
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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
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.
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.
Avatar of peispud

ASKER

Thank you