make table query converting field from fixed with 6 decimal places to scientific notation

[ChristineMD]

Once again, I have a problem that seems to occur in numerous flavors all over the web, but I cannot find solution to my specific problem. Drives me crazy that Access messes up the simplest things!

I have a make table query calculating some revenue metrics. Query results are correct. I've formatted them as fixed with 6 decimals in the query. If I view the results of the query (without ! running) the results are correct.

When I run the make table query, the results in the table are now in scientific notation

Even if I fomat the table field how I want it to be, and and do an append query instead of a make table query the numbers still are converted!

Just seems like this shoudl not happen. Or be easier to fix than five pages of code!

I played around with the format function, but it was so rigid, sometimes these results have a significant digit to the left of the decimal, and sometimes not - and that didnt' seem to work correctly - but it's definitely possible I was doing something wrong there

Anyone have any suggestions?

Thanks in advance

Christine

 

[xenou]

Hi,

I would recommend do not use a make table query if you need to preserve your field properties (or table indexes). Instead prefer to use two queries in turn:

1. Delete the old data from the existing table
2. Insert (append) the new data to the table

As far as I know there is no way to define the formats of fields in a make table operation - though typically they will follow the data types of the data in the query (if possible) - beyond that, all bets are off. I think generally they will also be created without a primary key which is generally something you want in most/all of your tables, even if it's just an autonumber ID.

ξ

 

[ChristineMD]

Thanks Xenou - I have used that method upon occasion - after the end user reviewed the report she decided she didn't need quite so many decimal places afterall, which seems to have resolved the issue. Which seems really weird to me, since Access likes to add oodles of decimal values to calculated fields when you don't specify any format, but oh well!

 

[xenou]

Which seems really weird to me, since Access likes to add oodles of decimal values to calculated fields when you don't specify any format, but oh well!

That crossed my mind as well! One of life's mysteries I guess.