round or truncate in excel 2002

[Gilbertjoe]

I have a query in MS Access 2002 that outputs to excel. In access I created a module that rounds and truncates numbers to the nearest tenth in an after update event on a form. Example: 1.45559999 will round up to 1.5 and truncate off anything else. When I pull the query into an excel spreadsheet. the numbers in the field display as the rounded/truncated field but are displayed in the formula bar like they were never rounded or truncated. How do I fix this?
Example: After the query is pulled into excel ....
Cell A1 will read as 3.8 but the formula bar shows it as 3.75
I want the cell and formula bar to be 3.8

 

[straybullet]

Although in the query the results are viewed as 1.5, for calculation purposes, the number is still actually 1.4559999. As far as I know, without changing the format of the information before exporting to excel, there is no way to change this.

 

[luceze]

Could you make another query with the same fields but use this formula =round(YourNumberField,1) on the field in question and then export?

HTH,
Eric

 

[chrisaroundtown]

I agree with Luceze.

In the access module it sounds as though you are just changing the format of the number, hence the number is still 1.4559999 however you are viewing it as 1.5.

To change the actual number in an access query use the ROUND function. The format is ROUND([Field],decimals) hence for one decimal place use ROUND([Field],1). The same formula works in excel so you could also do it after the data lands there.

You probably can do it in the access module but I am not sure of the code to do that.

Thanks
Chris