Truncating instead of rounding

[great_turtle]

I've inherited an Access database. It pulls the amount of pounds sold of our products from our sql server and converts to tons by pounds/2000. However, it truncates after 2 decimals which leads to a huge amount of error. I tried changing the format to 4 decimals but it just takes the existing 2 decimal truncated value and adds 2 zeroes to the end. How do I change this?

As an example, 20773.44 lbs gets converted to 10.38 tons when it is actually 10.38672 tons.

I'm using Access 2007 and can provide other info if needed.
Thanks!

 

[madaknarf]

You can try using Format(value,"00.0000"). It will force out the extra decimals. We would need to see your query to help further.

 

[xenou]

I don't know of anything in Access per se that would round and/or truncate this way. You will need to be more specific about the procedures. How is the data pulled from SQL server? When how does the calculation occur?

 

[Joe4]

And are you storing the calculation in a table? If so, how you have defined that field on the table level may determine how many decimal places it will keep.
Usually, there is no need to store a calculated field in a table when it can be done easily in a query.

 

[great_turtle]

Joe4, this is being done in a query (which does eventually end up in a table which gets read by excel).

xenou, here's the relevant bit of sql code. I'm proficient enough with sql to know that part is fine. It's Access and its formatting I'm struggling with.

Sum([LBS]/2000) AS Tons
FROM dbo_SALESHISTORY

madaknarf,

I tried using format() as below but it didn't help.

FORMAT(Sum([LBS]/2000),".####") AS Tons

 

[Joe4]

I don't know if it will make any difference, but I would just divide the total SUM by 2000, not each of the components like you are doing, i.e.

Sum([LBS])/2000 AS Tons
FROM dbo_SALESHISTORY

 

[great_turtle]

I knew it would be simple! Thanks Joe4 and everyone for helping.

 

[Joe4]

Glad it worked out!