formula in an access field to round an invoice value to nearest ?0c or ?5c.

  • Thread starter Thread starter defrag888
  • Start date Start date
D

defrag888

Guidance Please.....

I am a newbie to Access and I am trying to put a formula in an access field
to round an invoice value to nearest ?0c or ?5c.

To date I cannot find any assistance in how to do this. Is there anyone who
has done this and can assist.

I am in Australia and all cash sales are rounded to nearest ?0c or ?5c


Any assistance is appreciated


Haydn Bold.


hb20@[email protected]


"do as ye will, with harm to none"
 
Its easy to round to an integer, Just realise that 0.05 is 1/20 of an
integer amount.
So DisplayedValue = (int(InternalValue + 0.025 * 20))/20
The addition of 0.025 fixes the round up, where the integer function
truncates off the decimal bits.
Of course your InternalValue must be a field of type Currency or Double


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
I would do something like
If (value *10 - Integer(Value * 10) > 0.2 then
RoundedValue = Integer(Value *10) / 10
else
RoundedValue = Integer(Value*10)/10
end if
You can do this in a query of VBA.

Jim Moore
 
That's close, but both cases are calculating the
RoundedValue exactly alike. And it doesn't round up to the
next dime, but rounds 6.39 to 6.35 instead of 6.40. And it
frequently rounds up prematurely when the internal value
is calculated to to be slightly over the cutoff, such as
0.200000001. And a right parentheses was missing in some
lines. Try the following tested code:

If (Value * 10 - Int(Value * 10)) > 0.75 Then
RoundedValue = Int(Value * 10) / 10 + 0.1
ElseIf (Value * 10 - Int(Value * 10)) > 0.25 Then
RoundedValue = Int(Value * 10) / 10 + 0.05
Else
RoundedValue = Int(Value * 10) / 10
End If

John Loewen
 
I love your solution - much cleaner and more efficient.
However, I tested your code and it failed miserable until
I added a missing pair of parentheses. The line should
look like this to work correctly:
DisplayedValue = (int((InternalValue + 0.025) * 20))/20

John Loewen
 
Yes, you are correct, thats what happens when I write untested code. Glad
to know it solved the problem though.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Thanks guys for the info however the actual problem may have been missed.
I looking for code to round 1c to 0c, 2c to 0c, 3c to 5c, 4c to 5c and
similar for 6c,7c to 5c and 8c,9c, to 0.
It must look at the end figure. I tried with IIF statements but I stuffed
it.
The end result is $2.34 has to equal $2.35, $4.58 = $4.60 and $8.98 has to
equal $9.00. etc
I am looking at 3 boxes
Sub Total $ 4.58 $1.21
Rounding $ .02 $ -.01
Total $ 4.60 $1.20
Your input is realy appreciated by this newbie to Access programming.
It also interesting how many others have requested the same info privately.
I have refered them to the news group for updates

Haydn Bold.
hb20@[email protected]
"do as ye will, with harm to none"
 
The ( corrected ) code will do the round as you asked. To get the Rounding
number in your example, just subtract the Total from the SubTotal.

So the calculation goes

Total = RoundFunction(SubTotal)
Rounding = SubTotal - Total


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top