Rounding Calculations

[Guest]

I want to perform a series of calcuations in an update query. For eg (field1*.3)+(field2*.65)+ (field3*.05). Even though the display is set to 2 decimal places, Access uses all the decimal places resulting from each calculation in the result, and then rounds to 2 decimal places. I want to round as I go along, but can't find a way to do it. Any suggestions?

 

[Brian Camire]

If you're using Access 2000 or later, you can use the Round function in an
expression something like this:

Round(field1*.3, 2) + Round(field2*.65, 2) + Round(field3*.05, 2)

Note that Round uses "Banker's" rounding. In other words, it rounds 0.5 (or
decimal fractions thereof), either up or down, whichever will result in an
even number in the specified decimal place. For example:

Round(2.345, 2)

returns 2.34

and

Round(2.335, 2)

also returns 2.34 instead of 2.33.

Post back if you don't have Access 2000 and/or you want to round
differently.

I want to perform a series of calcuations in an update query. For eg

(field1*.3)+(field2*.65)+ (field3*.05). Even though the display is set to 2
decimal places, Access uses all the decimal places resulting from each
calculation in the result, and then rounds to 2 decimal places. I want to
round as I go along, but can't find a way to do it. Any suggestions?

 

[Guest]

Thanks, Brian, but unfortunately, I'm using an earlier version. We are running Office 97. Any suggestions for that

----- Brian Camire wrote: ----

If you're using Access 2000 or later, you can use the Round function in a
expression something like this

Round(field1*.3, 2) + Round(field2*.65, 2) + Round(field3*.05, 2

Note that Round uses "Banker's" rounding. In other words, it rounds 0.5 (o
decimal fractions thereof), either up or down, whichever will result in a
even number in the specified decimal place. For example

Round(2.345, 2

returns 2.3

an

Round(2.335, 2

also returns 2.34 instead of 2.33

Post back if you don't have Access 2000 and/or you want to roun
differently

I want to perform a series of calcuations in an update query. For e

(field1*.3)+(field2*.65)+ (field3*.05). Even though the display is set to
decimal places, Access uses all the decimal places resulting from eac
calculation in the result, and then rounds to 2 decimal places. I want t
round as I go along, but can't find a way to do it. Any suggestions

 

[Brian Camire]

In that case, you might try an expression like:

Int(field1*.3*100 + 0.5) / 100 + Int(field2*.65*100 + 0.5) / 100 +
Int(field3*.05*100 + 0.5) / 100

or even something like

CCur(Format(field1*.3, "0.00")) + CCur(Format(field2*.65, "0.00")) +
CCur(Format(field3*.05, "0.00"))

which are in theory functionally equivalent and, unlike the Round function,
always round up (that is, away from zero).

You might consider creating your own Round function in a module that uses
one of these approaches and then use this Round function in your query
instead.

Thanks, Brian, but unfortunately, I'm using an earlier version. We are

running Office 97. Any suggestions for that?

 

[Guest]

Thanks again. You've been a great help.