Bankers Rounding Issue

[canuck_1]

Hello,

I need to find a way to use normal symmetric rounding (always rounds .5 up) instead of the Bankers Rounding that access defaults to when using the round() function. My end goal is to have a query/report with 3 columns; one column for Reg_Hrs*Reg_Rate, one for OT_Hrs*OT_Rate, and a grand total column that sums the two.


I found a post on another forum recommending that I use the formula: Format(number_to_Format,"#.00"). This works in my query for this expression:


"Sum(IIf(IsNull([Lbr_TS_Dtl]![Reg_Hrs]),0,Format([Lbr_TS_Dtl]![Reg_Hrs]*[lbr_Ts_Dtl]![Reg_Rate],"#.00")))"


However, when I try to use it to sum up the totals for Regular rate and Overtime Rate using this:


Sum(IIf(IsNull([Lbr_TS_Dtl]![Reg_Hrs]),0,Format([Lbr_TS_Dtl]![Reg_Hrs]*[lbr_Ts_Dtl]![Reg_Rate],"#.00")) +
IIf(IsNull([Lbr_TS_Dtl]![OT_Hrs]),0,Format([lbr_TS_Dtl]![OT_Hrs]*[lbr_ts_Dtl]![Overtime_Rate],"#.00")))


I get the error:

The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.


I get the same error when I put the Format() formula on the outside of the IIF statement.


I also tried taking out the Sum() and changing the "Total:" field from Expression to Sum and I got a bunch of other errors doing that.


Is there:

A) Some way to fix my expression
B) A way to "Assign parts of the expression to variable" as per the error message's reccomendation
C) A way to sum the Reg and Overtime columns without getting the "Subqueries cannot be used...." error
D) a pre-made vba rounding function that I could put in here? (I am starting to think that shoul have been my first choice however, I have never created a vba function before so I have avoided this solution)


Thank you in advance!


Canuck_1

 

[jdraw]

You may get a function you could use from info at
http://www.xbeat.net/vbspeed/c_Round.htm#Round16

Round17() seems to work well and fast

Round16() is a 1 liner and works

 

[canuck_1]

Thanks jdraw!

I will try those out!

canuck_1

 

[Micromax]

Hi Canuck 1

I was going through some similar problem with rounding on a query I use for payroll.
It's sort of funny, because when I first tried it didn't seem to work,but the next day it did. I may have done something wrong.
Anyways, here's what I think for what it's worth.
1.Check you Capital T ans small t's and L's in your expression. Are they correct?

2. Format your fields on the design grid to Currency, except Hours, you may have to change that to Double in the table.

2. I use the nz function for nulls. This may work and eliminate your problem with the query results error.

3. This is the syntax for a feld that calculates the Overtime rate x 1.5 with the Round Function. The 2 is the number of decimal places.
OverTimeRate: Round(nz([HourlyRate])*1.5,2)

4. This is only a suggestion.Use RegularPay:nz([Reg Hours]*Round(Nz([Regular Rate],2)

OverTimePay:nz([Overtime Hours]*Round(nz([OvertimeRate],2)
Gross Wage:nz([OvertimePay]+nz([RegularPay].

I hope this helps.

Rob