Round Off (Up or Down)

  • Thread starter Thread starter Rashid Khan
  • Start date Start date
R

Rashid Khan

Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
 
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
 
oops sorry,
The formula Int(......) is against GrandTotal.

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
 
RoundOff = TotalDue - SubTotal

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
 
Hi Tina,
I think I have mixed up everything .. I am giving herewith the actuals from
my Report
Subtotal = 480.43 --> the formula is [Quantity]*[UnitPrice]
Freight = 123.00 --> the control source is Freight field
Sales Tax = 24.02 --> the formula is ([SalesTaxRate]*[Subtotal]*100)/100
Round Off = It should be -0.45 in this case --> the formula is [Total Due] -
[Subtotal]
Total Due = =Int([Subtotal]+[Freight]+[Sales Tax]+0.5)

I get 146.57 shown in the Round Off instead of -0.45. What can be the
problem?

TIA
Rashid

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
 
Round Off = It should be -0.45 in this case --> the formula is [Total
Due] -
[Subtotal]
Click to expand...
i believe the formula should be [Total Due] - ([Subtotal]+[Freight]+[Sales
Tax])

hth


Rashid Khan said:
Hi Tina,
I think I have mixed up everything .. I am giving herewith the actuals from
my Report
Subtotal = 480.43 --> the formula is [Quantity]*[UnitPrice]
Freight = 123.00 --> the control source is Freight field
Sales Tax = 24.02 --> the formula is ([SalesTaxRate]*[Subtotal]*100)/100
Round Off = It should be -0.45 in this case --> the formula is [Total Due] -
[Subtotal]
Total Due = =Int([Subtotal]+[Freight]+[Sales Tax]+0.5)

I get 146.57 shown in the Round Off instead of -0.45. What can be the
problem?

TIA
Rashid

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
Click to expand...
 
Thanks Tina. It works perfectly. Really appreciate for your time and
advice.
Rashid
tina said:
Round Off = It should be -0.45 in this case --> the formula is [Total Due] -
[Subtotal]
Click to expand...
i believe the formula should be [Total Due] - ([Subtotal]+[Freight]+[Sales
Tax])

hth


Rashid Khan said:
Hi Tina,
I think I have mixed up everything .. I am giving herewith the actuals from
my Report
Subtotal = 480.43 --> the formula is [Quantity]*[UnitPrice]
Freight = 123.00 --> the control source is Freight field
Sales Tax = 24.02 --> the formula is ([SalesTaxRate]*[Subtotal]*100)/100
Round Off = It should be -0.45 in this case --> the formula is [Total Due] -
[Subtotal]
Total Due = =Int([Subtotal]+[Freight]+[Sales Tax]+0.5)

I get 146.57 shown in the Round Off instead of -0.45. What can be the
problem?

TIA
Rashid

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3)
GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top