Setting Decimal Places in a Make Table Query (1 Viewer)

[Cirrus]

I have a make table query that pulls data from a table and a query joined on common fields. When I view the query in datasheet view, 2 decimal places are showing on certain fields. However, when I run the table, the number fields are not showing 2 decimal places unless there are values in those decimal places (eg: 8.00 is converted to 8 and 6.50 is converted to 6.5). The output table must have 2 decimal places for these fields (total of 15) AND the end user does not want to fiddle with the output table each time it is run to reset the decimal places for these 15 fields. Therefore, I am trying to set a fixed number of decimal places in the make table query so that it transfers to the output table. I have tried the following expressions in the Expression Builder without success:

• HtAT2m: FormatNumber([HeightAT2m],2) The output in the table is a text and it must remain a number.

• HtAT2m: CDbl([HeightAT2m]) The output in the table still strips off the zero decimal places.
• HtAT2m: Round([HeightAT2m],2) The output in the table still strips off the zero decimal places.

In the property sheet for the field in the query, I have also tried the following:

• Format is set to Fixed, Decimal Places set to 2, Input Mask set to #.##. Output table still strips off the zero decimal places.

Any additional ideas would be most welcome. Please let me know if more information is needed to help solve this problem.

 

[gemma-the-husky]

I don't understand the problem. The user should not be looking at tables. He should be looking at forms and reports, and there shouldn't be a problem in your forms and reports. Seriously, I hardly ever look at data in tables, and I don't bother about the presentation of the data in tables.

Instead of a make table query, can you not pre-design a table with the formatting you want, and load the data into that table, rather than use a make-table query.

You could also write code to change the format of field types after running the make-table query, although I would prefer the former option.

If you want to avoid rounding errors you can always use a currency data type. It doesn't mean you will show currency values in your forms and reports, it's just another way of maintaining precision with numbers.

 

[theDBguy]

Hi. Perhaps one possible solution is to not use a make-table query and simply use a delete and an append query together instead. Just a thought...

 

[The_Doc_Man]

You are mistaking data storage with data usage and data presentation. You ALWAYS store exactly what you were given but then for presentation purposes build queries that contain Format functions to convert table values to displayable values with precise formats.