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
Answer from mwurth on Stack OverflowAccess 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]));
How do I get numbers to round to 2 decimal places in an Access report?
SQL Round to 2 Decimal Points?? | Access World Forums
Rounding off to two decimal places in SQL - Stack Overflow
vba - Rounding decimal places in Access - Stack Overflow
Videos
You can use the RoundUp and RoundDown functions found here at Experts Exchange and here at GitHub.
Your usage would be:
Value = 27.605
Value1 = RoundUp(Value, 2) ' 27.61
Value2 = RoundDown(Value, 2) ' 27.6
These are the functions:
' Rounds Value up with count of decimals as specified with parameter NumDigitsAfterDecimals.
'
' Rounds to integer if NumDigitsAfterDecimals is zero.
'
' Optionally, rounds negative values away from zero.
'
' Uses CDec() for correcting bit errors of reals.
'
' Execution time is about 0.5µs for rounding to integer
' else about 1µs.
'
Public Function RoundUp( _
ByVal Value As Variant, _
Optional ByVal NumDigitsAfterDecimals As Long, _
Optional ByVal RoundingAwayFromZero As Boolean) _
As Variant
Dim Scaling As Variant
Dim ScaledValue As Variant
Dim ReturnValue As Variant
' Only round if Value is numeric and ReturnValue can be different from zero.
If Not IsNumeric(Value) Then
' Nothing to do.
ReturnValue = Null
ElseIf Value = 0 Then
' Nothing to round.
' Return Value as is.
ReturnValue = Value
Else
If NumDigitsAfterDecimals <> 0 Then
Scaling = CDec(Base10 ^ NumDigitsAfterDecimals)
Else
Scaling = 1
End If
If Scaling = 0 Then
' A very large value for Digits has minimized scaling.
' Return Value as is.
ReturnValue = Value
ElseIf RoundingAwayFromZero = False Or Value > 0 Then
' Round numeric value up.
If Scaling = 1 Then
' Integer rounding.
ReturnValue = -Int(-Value)
Else
' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
On Error Resume Next
ScaledValue = -Int(CDec(-Value) * Scaling)
ReturnValue = ScaledValue / Scaling
If Err.Number <> 0 Then
' Decimal overflow.
' Round Value without conversion to Decimal.
ScaledValue = -Int(-Value * Scaling)
ReturnValue = ScaledValue / Scaling
End If
End If
Else
' Round absolute value up.
If Scaling = 1 Then
' Integer rounding.
ReturnValue = Int(Value)
Else
' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
On Error Resume Next
ScaledValue = Int(CDec(Value) * Scaling)
ReturnValue = ScaledValue / Scaling
If Err.Number <> 0 Then
' Decimal overflow.
' Round Value without conversion to Decimal.
ScaledValue = Int(Value * Scaling)
ReturnValue = ScaledValue / Scaling
End If
End If
End If
If Err.Number <> 0 Then
' Rounding failed because values are near one of the boundaries of type Double.
' Return value as is.
ReturnValue = Value
End If
End If
RoundUp = ReturnValue
End Function
And:
' Rounds Value down with count of decimals as specified with parameter NumDigitsAfterDecimals.
'
' Rounds to integer if NumDigitsAfterDecimals is zero.
'
' Optionally, rounds negative values towards zero.
'
' Uses CDec() for correcting bit errors of reals.
'
' Execution time is about 0.5µs for rounding to integer
' else about 1µs.
'
Public Function RoundDown( _
ByVal Value As Variant, _
Optional ByVal NumDigitsAfterDecimals As Long, _
Optional ByVal RoundingToZero As Boolean) _
As Variant
Dim Scaling As Variant
Dim ScaledValue As Variant
Dim ReturnValue As Variant
' Only round if Value is numeric and ReturnValue can be different from zero.
If Not IsNumeric(Value) Then
' Nothing to do.
ReturnValue = Null
ElseIf Value = 0 Then
' Nothing to round.
' Return Value as is.
ReturnValue = Value
Else
If NumDigitsAfterDecimals <> 0 Then
Scaling = CDec(Base10 ^ NumDigitsAfterDecimals)
Else
Scaling = 1
End If
If Scaling = 0 Then
' A very large value for Digits has minimized scaling.
' Return Value as is.
ReturnValue = Value
ElseIf RoundingToZero = False Then
' Round numeric value down.
If Scaling = 1 Then
' Integer rounding.
ReturnValue = Int(Value)
Else
' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
' Very large values for NumDigitsAfterDecimals can cause an out-of-range error when dividing.
On Error Resume Next
ScaledValue = Int(CDec(Value) * Scaling)
ReturnValue = ScaledValue / Scaling
If Err.Number <> 0 Then
' Decimal overflow.
' Round Value without conversion to Decimal.
ScaledValue = Int(Value * Scaling)
ReturnValue = ScaledValue / Scaling
End If
End If
Else
' Round absolute value down.
If Scaling = 1 Then
' Integer rounding.
ReturnValue = Fix(Value)
Else
' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
' Very large values for NumDigitsAfterDecimals can cause an out-of-range error when dividing.
On Error Resume Next
ScaledValue = Fix(CDec(Value) * Scaling)
ReturnValue = ScaledValue / Scaling
If Err.Number <> 0 Then
' Decimal overflow.
' Round Value with no conversion.
ScaledValue = Fix(Value * Scaling)
ReturnValue = ScaledValue / Scaling
End If
End If
End If
If Err.Number <> 0 Then
' Rounding failed because values are near one of the boundaries of type Double.
' Return value as is.
ReturnValue = Value
End If
End If
RoundDown = ReturnValue
End Function
Note please, that the native Round of VBA is quite buggy. See the test module of the download.
You won't get a better answer than Gustav's for proper rounding functions, and in the case of only two constituent values, then the complimentary RoundDown and RoundUp functions are sufficient. (FYI, similar functions are often named Floor and Ceiling in other languages and programming libraries.)
However, when it is critical to have constituent values sum to an original/expected value, it is best to calculate the last of the constituent values by a difference of the expected total and the sum of all other rounded values. This will guarantee the correct sum even if there are unexpected results from rounding. Even if using the buggy VBA Round() function, this technique would have avoided the 1 cent errors to start with.
surcharge1 = SomeRoundFunction(Total_Surcharge / 2.0)
surcharge2 = Total_Surcharge - surcharge1
This is especially important with more than two constituent values, since there is no set of rounding functions that will properly round 3 or more values to guarantee they add up to some value. As an example, I recently had to split out total transaction amounts by (re)calculating discounts and taxes. Although I knew the percentages for both, I needed to figure each part rounded to the penny. Penny errors were acceptable in both discount and tax, but they still needed to add up to the total after any rounding. The only guarantee to ensure that the final transaction value remained unchanged was to first calculate and round the tax, next calculate and round the discount, then finally correct for penny errors by subtracting the sum of parts from the expected total.
All you need is:
CAST(255.87908765444 as decimal(18,2)).
When you convert data types in which the target data type has fewer decimal places than the source data type, the value is rounded. From microsoft
If you need it as a string, this should work:
select format(round(255.87908765444,2), 'N2');