Hi Ron
"Round" usually implies "round to nearest" What you want to do is round UP.
The CLng (and CInt) functions use "banker's rounding" (round to the nearest
integer, and round 0.5 to the nearest even integer).
If you want always to round down then use the Int function. If you want to
ignore the fractional part, then use the Fix function. These functions
return the same result for positive numbers, but not for negative numbers:
Int(6.8) = 6 Fix(6.8) = 6
Int(-6.8) = -7 Fix(-6.8) = -6
I suggest you use Int or Fix instead of the integer division operator (\)
because the latter rounds to the nearest integer first and results can be
unpredictable:
5.9 \ 6 = 1 (same as 6 \ 6)
while
Int(5.9 / 6) = 0 (same as Int(0.9833333))
So, if you want to round DOWN to the nearest multiple of 6, you can use:
Int( [value] / 6 ) * 6
However, rounding UP is a bit more tricky. This will not work:
(Int( [value] / 6 ) + 1) * 6
Because if [value] is already a multiple of 6, it will get rounded up to the
*next* multiple. For example:
(Int( 12 / 6 ) + 1) * 6 gives 18
So, you want to add 1 *only* if the original value is NOT already a multiple
of 6.
If the original value is always an integer, then you can test this with the
Mod operator:
[value] Mod 6 <> 0 ==> True if [value] is not a multiple of 6
If you want this to work with non-integral numbers then you must use this
test:
Int([value] / 6) <> ([value] / 6) ==> True if [value] is not a
multiple of 6
Now, you can use a trick that works because True has a numeric value of -1
and False has a value of 0. So, subtracting True from something is the same
as *adding* 1, while subtracting False leaves the value unchanged:
[value] - True = [value] + 1
[value] - False = [value]
Putting all this together, we have:
( Int( [value] / 6 ) + (Int([value] / 6) <> ([value] / 6) ) ) * 6
If you write a function to do this, you can store [value] / 6 in a temporary
variable:
Public Function RoundUp(vAmount As Variant, dRoundTo As Double) As Variant
Dim dTemp As Double
If IsNumeric(vAmount) Then
dTemp = vAmount / dRoundTo
RoundUp = (Int(dTemp) - (Int(dTemp) <> dTemp)) * dRoundTo
Else
RoundUp = vAmount
End If
End Function
then, simply call the function:
RoundUp( [value], 6 )
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Ron said:
Hi all,
I needed to round number to multiple of 6.
So, I used CLng([value]/6)*6 but sometimes the function round the numbers
down and that is not good for me.
Example: If the number is 20, then CLng((20)/6)*6 is 18 and I need 24.
Basiclly, I need to divide my number by 6, ignore the fraction, add 1 and
multiple by 6
Example:
20/6=3.333333333, 3+1 = 4, 4*6 = 24
I dont know which function to use or to express that in VBA.
Thanks,
Ron
Ron said:
Thanks.
Graham Mandeno said:
Hi Ron
This expression will round a value to the nearest multiple of 4:
CLng([Value]/4) * 4
In other words: divide it by 4, round it to the nearest whole number,
then multiply he result by 4.
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Hi,
I have a query that calculate sum of quantities.
Is it possible to take value and manipulate/round it so it will be
divide by 4?
What is the best way to approach this issue?
TIA,
Ron
