Insufficient number of decimal places in MAKETABLE query results –unable to resolve

[OB7]

I'm running an Access 2007 query on Windows 7, and one of my fields is a calculated value (X/Y), where X and Y are also fields within the query. When this query is run as a SELECT query, there are many (28) decimal places in the calculated field (which is what I want). However, if I run the same query as a MAKETABLE, the number of decimal places in that same field is limited to two, and thus I get a significantly different column total. I don’t understand what caused that to change--I made no changes to the criteria or the properties--I just changed from SELECT to MAKETABLE!



I attempted to correct this problem by opening the query in Design View, selecting the calculated field (X/Y), clicking on Properties, and entering "28" in the Number of Decimal Places field, and I tried entering both “Standard” and “Fixed” for the Format. However, I still have the same problem with the MAKETABLE query results showing only two decimal places.

In addition to being unable to change the properties from within the query (which would be ideal), I cannot even change the properties from within the resulting table! In the table, I’ve tried changing the Precision to 28, the Scale to 15, the Decimal Places to 15, and I’ve tried both a field size of “Decimal” and “Double,” as well as a Format of both “Standard” and “Fixed.” The only result is that the number of digits displayed is increased, but most of them (other than the two digits immediately to the right of the decimal point) are zeros!

Any information regarding why this is happening and what can be done to resolve it would be very much appreciated! Because this query is just one of many that I have included within a macro, I would like to be able to use MAKETABLE rather than SELECT. Thank you!

[mrojas]

Have you thought about creating a table and appending the results of your query to it? This might give more control over the field's property. However, I believe that the most number of decimal places for double, in Access, is 15.

[OB7]

Thanks for your response, mrojas! I found that I was able to control the number of decimal places in the target table by using the Round function in the SQL code in the MakeTable query.