Be careful, the VBA Round function uses Banker's rounding, where it rounds .5 to an even number, like so:
Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)
Whereas the Excel Worksheet Function Round, always rounds .5 up.
I've done some tests and it looks like .5 up rounding (symmetric rounding) is also used by cell formatting, and also for Column Width rounding (when using the General Number format). The 'Precision as displayed' flag doesn't appear to do any rounding itself, it just uses the rounded result of the cell format.
I tried to implement the SymArith function from Microsoft in VBA for my rounding, but found that Fix has an error when you try to give it a number like 58.55; the function giving a result of 58.5 instead of 58.6. I then finally discovered that you can use the Excel Worksheet Round function, like so:
Application.Round(58.55, 1)
This will allow you to do normal rounding in VBA, though it may not be as quick as some custom function. I realize that this has come full circle from the question, but wanted to include it for completeness.
Answer from Lance Roberts on Stack Overflowexcel - Rounding in MS Access - Stack Overflow
Solved - Rounding decimal places | Access World Forums
Ms ACCESS and SQL: round to two decimals through query - Stack Overflow
Rounding in access 97
Videos
Be careful, the VBA Round function uses Banker's rounding, where it rounds .5 to an even number, like so:
Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)
Whereas the Excel Worksheet Function Round, always rounds .5 up.
I've done some tests and it looks like .5 up rounding (symmetric rounding) is also used by cell formatting, and also for Column Width rounding (when using the General Number format). The 'Precision as displayed' flag doesn't appear to do any rounding itself, it just uses the rounded result of the cell format.
I tried to implement the SymArith function from Microsoft in VBA for my rounding, but found that Fix has an error when you try to give it a number like 58.55; the function giving a result of 58.5 instead of 58.6. I then finally discovered that you can use the Excel Worksheet Round function, like so:
Application.Round(58.55, 1)
This will allow you to do normal rounding in VBA, though it may not be as quick as some custom function. I realize that this has come full circle from the question, but wanted to include it for completeness.
To expand a little on the accepted answer:
"The Round function performs round to even, which is different from round to larger."
--Microsoft
Format always rounds up.
Debug.Print Round(19.955, 2)
'Answer: 19.95
Debug.Print Format(19.955, "#.00")
'Answer: 19.96
ACC2000: Rounding Errors When You Use Floating-Point Numbers: http://support.microsoft.com/kb/210423
ACC2000: How to Round a Number Up or Down by a Desired Increment: http://support.microsoft.com/kb/209996
Round Function: http://msdn2.microsoft.com/en-us/library/se6f2zfx.aspx
How To Implement Custom Rounding Procedures: http://support.microsoft.com/kb/196652
Access SQL has a rich function set one of which is the round function.
Example:
SELECT Round(Avg([Item Master].PlannedLeadTime),2) AS AverageLeadTime
FROM [Item Master]
WHERE (...)
Further information: http://www.techonthenet.com/access/functions/numeric/round.php
Use ROUND() Function:
The ROUND() function is used to round a numeric field to the number of decimals specified.
SQL ROUND() Syntax:
SELECT ROUND(column_name,decimals) FROM table_name;
|Parameter |Description
________________________________________________
|column_name |Required. The field to round.
|decimals |Required. Specifies the number of decimals to be returned.
So, Your required query will be:
SELECT ROUND(AVG([Item Master].PlannedLeadTime),2) AS AverageLeadTime
FROM [Item Master]
WHERE ((([Item Master].DateStamp)>=[Forms]![History Supplier Tool]![List2]
And ([Item Master].DateStamp)<=[Forms]![History Supplier Tool]![List3]) AND (([Item Master].SupplierName)=[Forms]![History Supplier Tool]![List1]));
This is not a bug, it is by intent. Access uses "banker's rounding: values ending in 5 are rounded to the nearest even digit, not to the next higher digit as usual. So 0.235 becomes 0.24 and 0.245 also becomes 0.24 when rounded to 2 decimal places.
See http://allenbrowne.com/round.html for an explanation.
I need VBA code to use
if you can, please