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

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"
 
A

Adrian Jansen

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
 
J

Jim Moore

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
 
G

Guest

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
 
G

Guest

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
 
A

Adrian Jansen

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
 
D

defrag888

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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top