Turn Off Automatic Rounding in Access

[ahuffman24]

Hi,

I am using Access 2016. I have a form with an Item_Number field where values need to be entered into tables as decimals. I do not want Access to round my decimals for me because sometimes I may need to type in, for example, the number 5.1, but other times I may need to type in 5.10. In my table where this field is stored, I have the field size set to double and the decimal places set to auto. In the form where the user types in the value, I have the decimal places set to auto and the format set to fixed. Currently, if I type in the number 5.1, it automatically sets it to 5.10. I have messed around with the decimal places setting, field size, and format to try and fix this, but it seems like no matter what, Access either rounds 5.1 to 5.10 every time, or it will round 5.10 to 5.1. Is there a setting that will allow the user to type in whatever they want to type in without Access rounding anything?



Thanks!

[JoeM]

Note that is not rounding at all. 5.1 and 5.10 are exactly the same values, it is just the number of decimal places that are shown. What you are referring to is Formatting (how many decimals to show).
For a number, a zero at the end of any decimal really has no meaning. It doesn't add/change anything to the value. As such, most systems automatically drop trailing zeroes in a decimal for numeric entries.
What you are referring to is Formatting (how many decimals to show).

In storing a numeric entry, Access will automatically drop any trailing zeroes after the decimal, regardless of how you enter it. If you want to maintain trailing zeroes, then you need to enter the values as Text, not Numbers (where all characters are retained). Of course, if you are hoping to apply mathematical functions on these entries, that could be problematic. However if you are not, this may work for you.

[ahuffman24]

Ah, I see. I do not have to do any mathematical operations, so this is working for me. thank you so much!

[JoeM]

You are welcome.

[ahuffman24]

@JoeM, scratch that. I have some buttons that navigate between forms, and I have conditions set on those buttons that will filter the next form by the item number. After changing the item number field to a short text, when I click on one of the buttons to navigate to a different form, I get an error that says "Data type mismatch in expression". I am thinking this is because the item number field is no longer a number. Any way to fix this?

[JoeM]

If you change the Data Type, you may need to rebuild any code that uses that field.
If you have placed those fields on your Form, you will need to replace that field with the new version (just delete the original field, and then re-add it from your list of available fields).

[ahuffman24]

I will try that. Thank you!

[ahuffman24]

Hi again. I tried what you said, and I am still receiving the same error. To give you some background on my database, I am using it to track orders from customers. The parts that the customer orders must be inspected before being shipped out. So, two of my forms are specifically for the inspection process. The first form allows the user to choose which order to inspect, and the second form is the actual inspection form. Here is a picture of the first form, where the user chooses which order to inspect:


When the user selects the command button next to an order, I have macros running in the background of the button that will filter the succeeding form based on the order number and item number of the selected order. Here is a picture of the macros:


Then, after selecting the button, this screen should appear with info about the selected order:


However, since I changed the data type of the item number field, every time I click the command button, I get this error:


I even tried changing the order number field to a short text as well, and I am still getting the error, and as a result I cannot navigate to the next form. Is there anything else I could try, or is there another way to fix the decimal issue without changing the data type of the item number field?

[ahuffman24]

And, I deleted and re-added the fields from the form, hence why those fields' text boxes are so much smaller than the rest

[JoeM]

I think the issue is with the WHERE command of your Imbedded Macro. When searching on Text fields, you must have text qualifiers around the values you are looking for. So instead of looking for:
5.1
you would be looking for
"5.1"
or
'5.1'
(SQL will usually accept either single or double-quote for text qualiferis).

So you need to change the part of your WHERE clause that says this:
=="[Order_Number]=" & [Order Number] & " AND...
to this:
=="[Order_Number]='" & [Order Number] & "' AND...
(so note the two single-quotes in red that I added).

[ahuffman24]

Is this assuming that both the Order Number and Item Number fields are short text data types? Also, do I need to add the single quotes around the portion of the macro that is for the Item Number field?

[JoeM]

Any text data field will need text qualifiers around the value it is returning for that condition.

[ahuffman24]

Okay, that seems to be working. Now, if I have a macro button filtered only by the Item Number field (like this: ="[Item_Number]="&[Item_Number] ), where would I add the single quotes?

[JoeM]

Same places as the last (should follow the exact same structure as the other one I showed you).

[ahuffman24]

Where you put your text identifiers assumes there are two fields involved in the macros. I only have one field, so the syntax is different. Would the text identifiers go here: ='"[Item_Number]="' & [Item_Number] ? I cannot put text identifiers around the second Item_Number in the same manner you did because there is no & or " that come after the second Item_Number