-0 C
Toronto
Friday, March 29, 2024

Tip: How to Solve, Microsoft Access Make Table results in Zero (0) Value

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:

FunctionReturn TypeRange for expression argument
CBoolBooleanAny valid string or numeric expression.
CByteByte0 to 255.
CCurCurrency-922,337,203,685,477.5808 to 922,337,203,685,477.5807.
CDateDateAny valid date expression.
CDblDouble-1.79769313486231E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
CDecDecimal+/-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.
CIntInteger-32,768 to 32,767; fractions are rounded.
CLngLong-2,147,483,648 to 2,147,483,647; fractions are rounded.
CSngSingle-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStrStringReturns for CStr depend on the expression argument.
CVarVariantSame 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 query to a Table, I hope this saves you a little time.

Leave a Reply

Mark Hanlon

Editor

Mark is an avid photographer, Starbucks addict, motivated cyclist, struggling runner, and rocking single parent living outside of Toronto, Ontario. Living with two chronic ilnesses, Crohn’s Disease and Diabetes, life for this Transportation Planner and Registered Professional Planner (RPP) can be an interesting mix.