24 C
Toronto

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 is a foodie in training, photographer, Starbucks addict, runner, cyclist, and single parent living outside of Toronto, Ontario. Living with Crohn’s Disease and Diabetes, life for this Transportation Planner and RPP can be an interesting mix.

780FollowersFollow
9FollowersFollow
139FollowersFollow

Must Read

Blogging 101: Catering to your Design Needs

Todays Blogging 101 assignment was to add and/or customize two widgets, one text-based and one image-based. Which sounds like great fun. The main reason why...

Blogging 101 – Add something new to your post – Take life by the Horns

Stand up and look around you?  What do you see?  For me, my first impressions of my hectic home office, are clutter...

Repost: Questions You Should Never Ask a Trail Runner

Trail Running is certainly an odd blend of community and introspection.  We are there when you misstep one of the three r’s...