Sum function at footer of form rounding issue

[motleyjew]

I am having a small problem when using the Sum function on the footer of my form. I am using the following:

=Sum([UnitQty]*[UnitCost])
UnitCost is a currency and UnitQty is a number

This is working except for one senario. Sometimes the quantity is a decimal. For instance, 27.75 X 0.5 = 13.875. It appears that when the sum is totaling this, it does not round up to 13.88 like I need it to. I also tried the following with no luck:

=Sum([UnitQty]*Round([UnitCost],2))

Any ideas?

Gregg

 

[dan231]

I can't offer a solution per say...but here is a link that explains rounding and why it may not work as you'd like it to.

http://www.tek-tips.com/faqs.cfm?fid=3734

 

[motleyjew]

Thanks Dan.

I actually just found a solution just like that. Thank you for taking the time to look for a solution. I do appreciate it. Here is what I found that I am using. Excuse me for not remembering where or who it came from.

Public Function RoundUP(RoundMe As Double)

Dim RoundAnswer As Double

RoundAnswer = Round(RoundMe, 2)

If RoundAnswer < RoundMe Then
RoundAnswer = RoundAnswer + 1
End If

RoundUP = RoundAnswer

End Function

I am calling it like this:

=Sum(Roundup([UnitQty]*[UnitCost]))

Thanks again,
Gregg

 

[dan231]

hey, if it works, it works

Glad you got your answer.

 

[Lee Chai Ling]

For rounding to 2 decimal places, try this :
=(int((Sum([UnitQty]*[UnitCost]))*100+0.5)/100)