Decimal Places not showing correctly in TextBox on Access form when clicking on control.

I have a textbox control set to standard format with 2 decimal places. It looks fine when you open the form. But if you click on the textbox control itself the decimal places then jump to 8. When you move the focus to another control it resets back to 2 decimal places. Why does it do that when you click on the control?
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

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

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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What is the datatype of the field? If that datatype is, for example, a Decimal and you've set it up at the table level to have 8 decimal places, then that's what Access will show.

You can control the way it's presented with an Input Mask, but that comes with a different set of troubles.
John TsioumprisSoftware & Systems EngineerCommented:
I think its the normal behavior...after all there is value that must be displayed correctly
ste5anSenior DeveloperCommented:
There are two masks involved:

the Format mask on the Format tab of the Property Sheet
the Input Mask on the Data tab.

When you jump into the TextBox, it switches to input/edit mode, thus now using the input mask. When you exit the box, editing is quit and the format mask is used.

Thus you need to set both masks.
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!

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
The datatype in the table is set to 3 decimal places as well. But for some reason it's showing 8 only when you click on the textbox. I put formatting code in the 'on click' event to show 3 decimal places and that fixes, or puts a bandaid on this issue. If this is normal behavior then I guess my bandaid will work just fine.
Gustav BrockCIOCommented:
If the user types 8 decimals, and you don't want these to be displayed later, you must round the value to two decimals after saving the value.

Access will not round the value "by itself", only round the display  of the value.
The format on the table does not control the actual data stored.  It is simply for display purposes.  When you click into the field to edit it, Access knows that you need to see the actual stored value and that is why the number of decimals might expand or contract.

Your data type is either single or double which is generally referred to as floating point since the number of actual decimal digits can vary.  If you want to restrict the decimal places stored, you need to use the rounding function on EVERY calculation.  Then I would remove the 3 from the number of decimal places defined on the table and leave it as auto.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
The datatype is stored as Double with 3 decimal places. Would changing that from double to another datatype like long integer make a difference, Pat?
Gustav BrockCIOCommented:
Integer and Long (and Byte) has no decimals, so that would prevent storing of any decimal.

If entering decimals, the values will be rounded before saving.
The datatype is stored as Double with 3 decimal places
Perhaps my post was not clear.  I said that the number of decimal places defined has NOTHING TO DO with what is actually stored.  It is simply for display purposes.  ALL FORMATTING specifications on tables are for display purposes and have nothing to do with what is actually stored.  The data type controls what is stored and Double is a FLOATING POINT data type.  It holds as many decimal places as are created by the calculation.

The ONLY data type that will allow you to store a user defined fixed number of decimal places is Decimal.  Decimal is actually a scaled integer.  That means the data is stored as a whole number.  The number of decimal places is controlled by an IMPLIED decimal point.  So Access uses integer arithmetic and then adjusts the decimal point at the end to round to whatever you specified.

Currency is fixed at 4 but nothing is fixed at 3.

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