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

Avatar of undefined
Last Comment
peispud

8/22/2022 - Mon
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.
peispud

ASKER
No. The field does not refer to a calculated field.

There are several thousand records.  Less than 10, 000
Tusitala

Ok, so in in the design view of your table, can you tell me what is the setting for "Decimal Places"?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
peispud

ASKER
It's set to currency.   I checked and it was set to auto
Tusitala

Ok, change the field "Decimal Places" setting from "Auto" to 2 and tell us if that fixes it.
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Tusitala

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Luke Chung

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 Brock

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
PatHartman

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 Chung

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 Brock

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Luke Chung

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

ASKER
Thank you