How can you use Microsoft Access Make Table Query and ensure you do not round decimial places?
How to A common issue with Microsoft Access is that when using the Make Table Query command, the new table is will create number fields as an integer and truncates your decimal places resulting in the number zero (0) to be written to the newly created Table / column. For example, if you had a field in your query that calculates a percentage, i.e. 0.78 or 78%, the integer removes all digits to the right of the decimal and stores simply the number zero (0) in your table. Here is a quick, and very simple, solution based on built-in data conversion functions (don’t worry it took me a bit of research across the internet to find it myself, and more simple than a few dozen lines of VB code that are commonly recommended)
Type Conversion Functions
Microsoft has several Type Conversion Functions built-in that can be utilized to manipulate the filed type on the fly. The function name determines the return type as shown in the following:
|Function||Return Type||Range for expression argument|
|CBool||Boolean||Any valid string or numeric expression.|
|CByte||Byte||0 to 255.|
|CCur||Currency||-922,337,203,685,477.5808 to 922,337,203,685,477.5807.|
|CDate||Date||Any valid date expression.|
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
|CDec||Decimal||+/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is|
+/-7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001.
|CInt||Integer||-32,768 to 32,767; fractions are rounded.|
|CLng||Long||-2,147,483,648 to 2,147,483,647; fractions are rounded.|
|CSng||Single||-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.|
|CStr||String||Returns for CStr depend on the expression argument.|
|CVar||Variant||Same range as Double for numerics. Same range as String for non-numerics.|
CDbl function to convert an expression to a Double number. In my Make Table Query, the Function can be applied as an “expression” directly in the query that will force the Make Table to use a Double Number.
Expression Name: CDbl(Table Name.Column) for example: Total_Percent: CDbl(Calc_Table.Perct)
I know many tech-savvy Access users would argue, why not just run from the query, what is the benefit of making a table? Well for some, including myself, there are a few programs out there that can read the MS Access data Tables, but not necessarily the queries, so we have no choice. No matter what your reasoning is for having to convert decimals in a query to a Table, I hope this saves you a little time.