MS Access Currency Allow Max Characters Right Of The Decimal

I need some quick advice please on how to control only 2 characters after the decimal in a table field. I have "Price" set with Decimal 2 and as Currency. But whenever someone types in $1.395 or messes up on the price Access just rounds it up and displays it as $1.40 to us the user and that is fine but when I export it to a CSV file it exports $1.395 and then the website imports it as $1,395.00

So how can I correct this issue and only allow the user to put in 2 characters after the decimal?

Unfortunately none is willing to buy a $1.39 screw for $1,395.00 or I would be filthy rich :)

Also as a side note some of the values in the "Price"field is really weird. Like for a $10.46 item it says "10.4625"???? I know noone messed up that badly. I hope!  Any thoughts???

Thanks for the help!
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.
0
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
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?
0
Determine the Perfect Price for Your IT Services

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

Dustin StanleyEntrepreneurAuthor Commented:
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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
OK, so in the Export to CSV, you are using a query?

If so, try modifying that field like:

Round([FieldName], "#0.00")
0
Dustin StanleyEntrepreneurAuthor Commented:
Round([ItemPrice], "#0.00")

Returns Errors
0
Dale FyeOwner, Developing Solutions LLCCommented:
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

Open in new window

0
PatHartmanCommented:
If the users are not entering the extra decimal digits, then perhaps they are the result of a calculation.  Always round the results of calculations if you want to limit the decimal digits.  I would also use the Currency data type rather than single or double.  Don't confuse the Currency data type with the Currency format.  The currency data type is a scaled integer and limited to four decimal digits.  It also does not suffer from floating point issues.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
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

Open 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])), ".", ",")

Open in new window

as Str always returns a dot as the decimal separator.
0
Dustin StanleyEntrepreneurAuthor Commented:
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!
0
Dustin StanleyEntrepreneurAuthor Commented:
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)

Open in new window

working
0
Gustav BrockCIOCommented:
If some values have four decimals by some error while two is what it should be, then round the values. But you should refrain from the native Round and use RoundMid. Syntax is identical:

RoundMid([Price],2)

Open in new window


Except, of course, if you find the source of error and realize, that values should be rounded up or down, not rounded by 4/5.
If so:

RoundUp([Price],2)
' or:
RoundDown([Price],2)

Open in new window

0
Dustin StanleyEntrepreneurAuthor Commented:
Thank you for the help. It had to be a calculation error. I ran the round function to clean up the numbers correctly. Thanks!
0
PatHartmanCommented:
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.
0
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.