Query Calculation Decimal Point Removal

[Robb @ FLW]

I have created a Calcuatlion field in a query,
[field]*[field1]*[field2]*[field3]/ 12 the result number is 5.333333 since it
is 5.3 I don't want to round up how can I remove the numbers following the
decimal point, also if it was 5.6 i would want it to round up to 6.

Thanks

 

[Clifford Bass]

Hi Robb,

Unless I am misunderstanding... Try:

Round([field]*[field1]*[field2]*[field3]/12,0)

Clifford Bass

 

[John Spencer]

Do you want to round the value to the nearest integer? If so
Round([field]*[field1]*[field2]*[field3]/ 12,0)

The built-in round function uses Banker's rounding.
5.5 will round to 6 and 6.5 will round to 6
In other words, it will round to the nearest even number if the decimal
portion is exactly .5 If the decimal portion is .500000000001 then the
numbers would round to 6 and 7 respectively.

So you can force the rounding by adding a small increment before rounding the
number or you can use VBA functions to do roundup, rounddown, round as most
people do.

In your situation, I might use
Round(.00000001 + ([field]*[field1]*[field2]*[field3]/ 12),0)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Clifford Bass]

Hi Robb and John,

Adding a fractional amount is prone to errors. Suppose your
calculation result is 5.49999999? You will get 6 as a result instead of 5.
Or if your calculation result is -5.5. You will get -5 instead of -6.
Better to use something like this:

Sgn([field]*[field1]*[field2]*[field3]) *
CInt(Abs([field]*[field1]*[field2]*[field3]/12) + 0.5)

Or more generally, for rounding to the larger absolute value when the
value is mid-way, you might write your own function:

Public Function RoundLarger(ByVal dblValue As Double, _
ByVal intDecimals As Integer) As Double

RoundLarger = Sgn(dblValue) * CInt(Abs(dblValue) * _
(10 ^ intDecimals) + 0.5) / (10 ^ intDecimals)

End Function

And then use that in place of the built-in Round() function. This
function will also allow the specification of a negative for the decimals so
you can round whole numbers to the nearest tens, hundreds, thousands, etc.
Something the built-in Round() function will not do.

? Round(5.5, 0)
6
? RoundLarger(5.5, 0)
6
? Round(6.5, 0)
6
? RoundLarger(6.5, 0)
7
? Round(-5.5, 0)
-6
? RoundLarger(-5.5, 0)
-6
? Round(-6.5, 0)
-6
? RoundLarger(-6.5, 0)
-7
? Round(123.455, 2)
123.46
? RoundLarger(123.455, 2)
123.46
? Round(123.465, 2)
123.46
? RoundLarger(123.465, 2)
123.47
? Round(555, -1)
[error 5]
? RoundLarger(555, -1)
560
? Round(565, -1)
[error 5]
? RoundLarger(565, -1)
570

Clifford Bass

 

[Clifford Bass]

Hi Robb and John,

Okay, that causes errors if the numbers get too large. So try this
instead:

Public Function RoundLarger(ByVal dblValue As Double, _
ByVal intDecimals As Integer) As Double

Dim dblTemp1 As Double
Dim dblTemp2 As Double

dblTemp1 = 10 ^ intDecimals
dblTemp2 = Abs(dblValue) * (10 ^ intDecimals) + 0.5
RoundLarger = Sgn(dblValue) * Int(dblTemp2) / dblTemp1

End Function

Clifford Bass

 

[Clifford Bass]

Hi,

Okay, I am really starting to get irritated at VBA. Even this gives
bad results at times. Sometimes, probably due to tiny differences in
calculated values, Int(whole_number) converts to whole_number - 1. So if I
have:

Public Function RoundLarger(ByVal dblValue As Double, _
ByVal intDecimals As Integer) As Double

Dim dblTemp1 As Double
Dim dblTemp2 As Double

dblTemp1 = 10 ^ intDecimals
dblTemp2 = Abs(dblValue) * dblTemp1 + 0.5
Debug.Print "dblTemp2 = " & dblTemp2
Debug.Print "Int(dblTemp2) = " & Int(dblTemp2)
RoundLarger = Sgn(dblValue) * Int(dblTemp2) / dblTemp1

End Function

And enter

? RoundLarger(2.445, 2)

I get:

dblTemp2 = 245
Int(dblTemp2) = 244
2.44

I would guess that dblTemp2 is really stores as something like
244.999999999999.

If anyone has a solution that works, I am open to it.

Clifford Bass

 

[James A. Fortune]

Hi,

Okay, I am really starting to get irritated at VBA. Even this gives
bad results at times. Sometimes, probably due to tiny differences in
calculated values, Int(whole_number) converts to whole_number - 1. So if I
have:

Public Function RoundLarger(ByVal dblValue As Double, _
ByVal intDecimals As Integer) As Double

Dim dblTemp1 As Double
Dim dblTemp2 As Double

dblTemp1 = 10 ^ intDecimals
dblTemp2 = Abs(dblValue) * dblTemp1 + 0.5
Debug.Print "dblTemp2 = " & dblTemp2
Debug.Print "Int(dblTemp2) = " & Int(dblTemp2)
RoundLarger = Sgn(dblValue) * Int(dblTemp2) / dblTemp1

End Function

And enter

? RoundLarger(2.445, 2)

I get:

dblTemp2 = 245
Int(dblTemp2) = 244
2.44

I would guess that dblTemp2 is really stores as something like
244.999999999999.

If anyone has a solution that works, I am open to it.

Clifford Bass

What happens when you try dblTemp2 = CDec(Abs(dblValue) * dblTemp1 +
0.5) in your function?

James A. Fortune
(e-mail address removed)

 

[Clifford Bass]

Hi James,

That is a pretty good suggestion. So far in my testing it seems to
deal with the issue. I am able to shorten the function into:

Public Function RoundLarger(ByVal dblValue As Double, _
ByVal intDecimals As Integer) As Double

Dim dblTemp As Double

dblTemp = 10 ^ intDecimals
RoundLarger = Sgn(dblValue) * Int(CDec(Abs(dblValue) * _
dblTemp + 0.5)) / dblTemp

End Function

The only issue is that it limits the size of the values down to a
smaller range. From approximately 10^(+308/-324) down to approximately
10^(+/-28). For my purposes so far, that is not an issue.

Thanks for the suggestion!

Clifford Bass