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 then 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.
CDbl Double -1.79769313486231E308 to
-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.

The Solution

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 querie to a Table, I hope this saves you a little time.

Leave a Reply