Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
problem with rounding! 6
- Thread starter adsfx
- Start date
This shouldn't be a problem. Each detail section is displaying the rounded value and the total is accurately totaling the values and display the rounded total.
You could display more decimal places in all text boxes or you could display inaccurate totals.
Duane
MS Access MVP
You could display more decimal places in all text boxes or you could display inaccurate totals.
Duane
MS Access MVP
-
2
- #3
koresnordic
IS-IT--Management
The only thing I can think of, is to use a query to output all your fields, except the value field. This is replaced by the expression "val:round([value],2)". This will round the value field to two decimal places. Therefore using this instead of the original data source will only give two decimal places and the values will agree to the total. Although as Duane has stated above, this will not give an accurate value, but it will make sense on the report.
![[pc]](/data/assets/smilies/pc.gif "[pc] [pc]")
Graham
Graham
koresnordic
IS-IT--Management
Did you resolve this in the end? It's nice to know as other people may have a similar problem, and could use the solution if it works.
Graham
Graham
koresnordic
IS-IT--Management
Thanks. Nice to see people refering to old posts once in a while.
Graham
Graham
-
1
- #7
Correct me if i'm wrong, but the round function in vb/vba isn't really correct ex
round(1.125,2) returns 1.12 in stead of 1.13
round(1.135,2) returns 1.13 ...
To prevent this we wrote our own function the display it correct.
Public Function MyRound(paValue As Double, paPrecision As Integer) As Double
Dim loSign As Integer
Dim loRoundPart As Double
Dim loFactor As Double
loSign = Math.Sgn(paValue)
loFactor = MyExp(10, CDbl(paPrecision))
loRoundPart = 0.5 / loFactor
MyRound = Fix((paValue + loSign * loRoundPart) * loFactor) / loFactor
End Function
Public Function MyExp(paValue As Double, paExp As Double) As Double
MyExp = Exp(paExp * Log(paValue))
End Function
Regards
Jurgen
round(1.125,2) returns 1.12 in stead of 1.13
round(1.135,2) returns 1.13 ...
To prevent this we wrote our own function the display it correct.
Public Function MyRound(paValue As Double, paPrecision As Integer) As Double
Dim loSign As Integer
Dim loRoundPart As Double
Dim loFactor As Double
loSign = Math.Sgn(paValue)
loFactor = MyExp(10, CDbl(paPrecision))
loRoundPart = 0.5 / loFactor
MyRound = Fix((paValue + loSign * loRoundPart) * loFactor) / loFactor
End Function
Public Function MyExp(paValue As Double, paExp As Double) As Double
MyExp = Exp(paExp * Log(paValue))
End Function
Regards
Jurgen
Round does bander rounding. This will depend on the odd or even value of the last digit you want to see. If it is even, it will round down and if odd, it will round up. This is designed to attempt to make the final results more accurate.
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
-
3
- #11
PaulBricker
Programmer
This expression will work almost anywhere. In a query, control source or function.
myVal:Int((myField+.005)*100)/100
123.456 returns 123.46
123.455 returns 123.45
It doesn't return the same results as the Round.
Paul
myVal:Int((myField+.005)*100)/100
123.456 returns 123.46
123.455 returns 123.45
It doesn't return the same results as the Round.
Paul
gearhead03
Technical User
I am having a similiar problem as this. I need to round each line item in the report seperately and then sum the total. I need it to round correctly - not mostly correct. I am using access97. The "round" function doesn't seem to work for me at all. However if it doesn't round correctly (i.e. 0-4 down 5-9 up) it is useless to me. My report displays the invoice total on each line item. So times we charge on half cent rates (i.e. 51 boxes * $1.055 = $53.805) this is invoiced as $53.81. When you get 10-20 of these in a report and "=Sum([grossinvoice])them it's off by .10 -.15 which is unacceptable. Has anyone tried Jurgen's function and does it work? Any help would be appeciated.
PaulBricker
Programmer
Access 97 doesn't have the Round function in it and it wouldn't return what you want anyway. I'm not entirely sure this will do what you want because I haven't tested it on a lot of numbers but try this expression in some sample data.
myVal:Int((number + .0055)*100)/100
That may do it for you.
Paul
myVal:Int((number + .0055)*100)/100
That may do it for you.
Paul
CosmoKramer
Programmer
gearhead03,
I'm probably missing something, but couldn't you use Paul's expression on the summed amount only?? In other words, add up the 3 decimal place items and only "round" the total.
Hoc nomen meum verum non est.
I'm probably missing something, but couldn't you use Paul's expression on the summed amount only?? In other words, add up the 3 decimal place items and only "round" the total.
Hoc nomen meum verum non est.
gearhead03
Technical User
PAUL,
Thanks for your help!! That works GREAT!
COSMO,
To answer your question, if you had the folowing values;
$1.555
$1.555
$1.555
$1.555
$1.555
Would be $7.78 your way.
the same values rounded first (1.56) would return $7.80
In this small example it is only .02 but you can see how it would add up quickly if you have lots of invoices.
Thank you everyone for you help
Thanks for your help!! That works GREAT!
COSMO,
To answer your question, if you had the folowing values;
$1.555
$1.555
$1.555
$1.555
$1.555
Would be $7.78 your way.
the same values rounded first (1.56) would return $7.80
In this small example it is only .02 but you can see how it would add up quickly if you have lots of invoices.
Thank you everyone for you help
- Status
Similar threads
- Locked
- Question
- Locked
- Question