MS Access Currency Allow Max Characters Right Of The Decimal

Use a proper rounding function to clean up the last decimals:

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

When exporting, force a format on the numbers that fits the import at the website.

Thanks Gustav. But why is Access doing this? I seriously do not think anyone is messing up this badly and adding 4-5 digits after the decimal.

Currency data types are accurate to 4 digits,  but that has nothing to do with the import at the website.  You need to check the file that you are exporting and see what the format looks like in that CSV file.  I'm guessing that the output to CSV is not formatting the way you expect.

Are you in Europe or another country which uses a comma as a decimal separator?

Dale I am in the USA and the CSV format looks just like the table with sometimes 4 digits to the right of the decimal. So it is somewhere before the export in Access.

Round([ItemPrice], "#0.00")

Returns Errors

Actually, you might want to try using the AfterUpdate event of that control, something like:

Private Sub txt_FieldName_AfterUpdate

     me.txt_FieldName = Round(me.txt_FieldName, 2)

End Sub

Select allOpen in new window

But why is Access doing this? I seriously do not think anyone is messing up this badly and adding 4-5 digits after the decimal.

Access and Currency doesn't "invent" or "create" anything not entered, but you wrote:

But whenever someone types in $1.395 or messes up on the price ..

thus, users are the root of the issue.

Access just rounds it up and displays it as $1.40 to us the user

No, Access doesn't "just" do anything, it just obeys your command:

I have "Price" set with Decimal 2

The decimal setting, however, is for display only. The true value is preserved, and if you export this as is, the value of 1.395 will be exported.

Now, users in general are not fools, so - as Pat mentions - an input of 1.395 is probably a result of a calculation somewhere else. As, when entered, it displays as 1.40, the user may have got the impression that the value is rounded automatically.
Don't blame them for that, but add that feature for the control:

Public Sub NetPrice_AfterUpdate()

    Me!NetPrice.Value = RoundMid(Nz(Me!NetPrice.Value, 0), 2)

End Sub

Select allOpen in new window

using the function I linked to above. Don't use the native Round as it is buggy.

As for the export, if $1.39 converts to $1,395.00 and you are in the US using dot as the decimal separator, this indicates that you export to an international site the expects comma as the decimal separator and dot as the thousand separator.
If you cannot change this, create a query to convert your values and use that for the export. The expression for your amounts could be:

NetPriceExport: Replace(LTrim(Str([NetPrice])), ".", ",")

Select allOpen in new window

as Str always returns a dot as the decimal separator.

Thank you for the advice. Tomorrow I am going to have time to go through it. I will double check the currency data type and see if there is any calculation problems. I don't honestly believe anything is being calculated but who knows. It has to be something because there are several records like this. Really weird!

I just logged in to look really quick and the "Price" field is currency data type. Decimal "2". I believe this may end up being a calculation issue. It seems to be only on the records that were programmatically imported. I just now need to track down what happened.  Is there a way to go down through the table in the field "Price" and programmatically round the numbers up?


So far I have

Round([Price],2)

Select allOpen in new window

working

Thank you for the help. It had to be a calculation error. I ran the round function to clean up the numbers correctly. Thanks!

Did you change the calculation to round at that time?

The best solution is probably to round in the query that is exported to excel since that is where the extra decimal places cause a problem.

Pat

he best solution is probably to round in the query that is exported

Yes I placed the round at the very last query so if anything slips by it would catch it.