Rounding error in Payroll calculations 5

[LD1010]

Thanks for reading my post.

I'm working on a payroll db and stumped on understanding why I'm getting rounding errors in a totals querry that calculates total pay.

I start with a query that calculates hours worked (in minutes) from tblLaborHours. TimeStart, LunchStart, LuchEnd, TimeEnd - all are Date/Time data type fields.

All the various times are calculated for each record TotHrs: DateDiff("n",[TimeStart],[TimeEnd]) resulting in total minutes, ect.

From a totals query I get the total hours per day and determine any OT and DT.

Now I join the totals query with a MaxOfPayRate from tblPayRate ([PayRate] and [PayRateC] are currency data types)

I get no rounding errors in the calculation for regular pay calc

RegPay: IIf([RateType]=1,([TotHrs]/60)*[PayRate],IIf([RateType]=2,([TotHrs]/60)*[PayRateC],0))

I do get the rounding errors for the overtime calc - RateType 2 employees are commissioned piece workers and their rate for OT is derived by taking their commissions earned for the week / by the number of hours worked * 1.5 resulting in [PayCommOTRate]

OTPay: IIf([RateType]=1,([OTHrs]/60)*([PayRate]*1.5),IIf([RateType]=2,(([OTHrs]/60)*[qryCommOTRate].[PayCommOTRate]),),0))

Any help would be much appreciated!

 

[SkipVought]

Hi,

Float arithmetic will give you errors.

Post some of the specific values that you are using, the expected calculated value and the actual calculated value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[LD1010]

Thanks for your help with this Skip.

Example of specific values use in the OT calc

IIf([RateType]=1,([240]/60)*([$15.25]*1.5)
Actual calculated value $91.48
Expected calculated value $91.50

IIf([RateType]=2,(([240]/60)*[13.39])
Actual calculated value $53.57
Expected calculated value $53.56

 

[lameid]

What are the datatypes of your fields?

 

[lameid]

SELECT (240/60)*(15.25*1.5) AS Expr1;

This returns 91.50... As a rule of thumb Access tends to do float math relatively well as long as the full expression is done at once.

If you were using VBA and did iterative calculations I would expect an error... or more significant one. BTW the workaround for that scenario that I have found is to build an arithmetic expression as a string and pass it to Eval rather than storing iterative results.

Back to the rates... you include a dollar sign.... are these really stored with two decimals or are they just formatted to currency (rounded)?

 

[LD1010]

Hi lameid, thanks for taking the time to responding to my post.

I'm not using VBA. I included the "$" in the examples only to indicate that the pay rates used come from the pay rate tables with the format set "currency", decimal places set "auto"

The field [PayCommOTRate] from qryCommOTRate is also formated "currency". The commission amount used to calc that rate comes from a table where the commission amounts for the pay periods are stored. That field is also set to format "currency", decimal places set "auto"

 

[TheAceMan1]

How are ya LD1010 . . .

See if [blue]type casting[/blue] will help:

[blue] IIf([RateType]=1,CCur([OTHrs]/60)*CCur([PayRate]*1.5),IIf([RateType]=2,(CCur([OTHrs]/60)*[qryCommOTRate].[PayCommOTRate]),),0))[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[jsteph]

Ld1010,
Aceman is right but even the constants should be cast for good measure--ie, ccur(1.5) instead of 1.5 because constants with decimal are implicit floats.

Currency is a scaled-double-long integer and won't give you rounding issues. A constant like 1.5 will introduce the float precision issues even if all other factors are integers.
--Jim

 

[JoeAtWork]

IIf([RateType]=2,(([240]/60)*[13.39])
Actual calculated value $53.57
Expected calculated value $53.56

That field is also set to format "currency", decimal places set "auto"

Your "formatting" is probably hiding what the numbers really are. For example, your 13.39 might actually be 13.3925 (which would give you the $53.57 result).

When you are building one query on another, you must round each step of the way.

 

[LD1010]

Thanks to all; SkipVoughtm, Lameid, TheAceMan1, jsteph and JoeAtWork for your contributions to my education. Your generosity in sharing your knowledge and experience always amazes me.

TheAceMan1 and jsteph's comments about "typecasting" were very helpful. This was something I knew nothing about.

JoeAtWork made me see the light and the error of my way. The formatting of the number was throwing me off. I had not thought about rounding at each calculation of each dependent query.

Thanks again for your help!!