I have numeric data (single, with three decimal places) that I am using in a calculated query. I have found online that calculated queries do not preserve the formatting of the incoming data, and so because of the default 'float' formatting, my data ends up with a whole bunch of significant figures well beyond the three that there should be. Setting the query to be fixed with three decimal places, or using the round function, displays the data correctly on screen, but if I actually click on the cell the multiple decimal places are still there.
I have been able to convert the data to a string format a couple of different ways, but I end up copying data into excel later and I don't want to spend all my time converting the data back to numbers there.
I have also been successful by using the currency format on the data, however wherever I have no value, it returns an #error message. In my particular spreadsheet this happens often and onscreen it just looks like a mess. I have used the Nz() function but it ends up creating a zero value (0.00) in all the fields instead of just nothing.
So my question is either: Is there another way of formatting my data to display just three significant figures, or is there someway I can format my currency formatted data to properly display null values?
Thanks.