Problem in SUM

[CEH]

Have a little problem I can't locate... Have a field on a report, all fields are unbound, for the sum of commisions... control is =Sum([CommissionPayable])
CommissionPayable is set to currency and decimal 2.... CommissionPayable source is rather lengthy....
CommissionPayable: IIf([TxtDateDiff]>365 And [JobLeadAssigned]=True,([Commission]/6),IIf([JobLeadAssigned]=True,([Commission]/2),IIf([TxtDateDiff]>365 And tblJobs.ServiceTypeID=1,([Commission]/3),IIf([CommissionPaid]=True,0,[Commission]))))
Property set to currency.......
This field draws on "Commission" field.... Lengthy too.......
Commission: IIf([JobInvoicePaid]=True And tblJobs.ServiceTypeID=1 Or [JobInvoicePaid]=True And tblJobs.ServiceTypeID=7 Or [JobInvoicePaid]=True And tblJobs.ServiceTypeID=5 Or [JobInvoicePaid]=True And tblJobs.ServiceTypeID=3,(([ContractPrice])-Nz([JobSubAmount]))*0.03,IIf([JobInvoicePaid]=True And tblJobs.ServiceTypeID=2,(([ContractPrice])-Nz([JobSubAmount]))*0.05,0))
Also set to currency.......
But the commission payable is adding up wrong........ Any guesses as to where I am getting the addition error????

 

[Brianwarnock]

Couldn't the commission be simplified to

Commission:IIf([JobInvoicePaid]=True And tblJobs.ServiceTypeID=2,(([ContractPrice])-Nz([JobSubAmount]))*0.05,(([ContractPrice])-Nz([JobSubAmount]))*0.03)

for Commission payable you can only get here
[IIf([TxtDateDiff]>365 And tblJobs.ServiceTypeID=1

if [JobLeadAssigned] is False is that what is expected?


Brian

 

[SOS]

And I would not "hard code" the commission percent. Use a table and a DLookup to get the values. Then when it changes (and it WILL change someday) you just change the rates in the table.

 

[CEH]

K....... first the questions.........
"Couldn't the commission be simplified to

Commission:IIf([JobInvoicePaid]=True And tblJobs.ServiceTypeID=2,(([ContractPrice])-Nz([JobSubAmount]))*0.05,(([ContractPrice])-Nz([JobSubAmount]))*0.03)"

No, there are other type ID's besides listed that calculations do or do not apply to, so it had to be specified....

"for Commission payable you can only get here
[IIf([TxtDateDiff]>365 And tblJobs.ServiceTypeID=1

if [JobLeadAssigned] is False is that what is expected?"

Well, I'm looking at a record that has datediff of 489....lead assigned =True... and it has a Commission........ So how ever screwy it looks..... It's working. There might be more in here your not seeing.... I just posted those fields to show where figures were coming from to find the error in addition.


So.......... Back to the problem..... Why am I getting rounding on the sum field?


Also... Yes, SOS, If I was wanting to spend some time redoing it (its an old, one of my first DB,s) I would put the rates in a table. And they'd have to give me a raise before I would rebuild it for them

 

[Brianwarnock]

You now say that it is a rounding error, what does the commissionpayable look like in the query, does that have any values after the decimal point?

Brian

 

[CEH]

In the query all fields are set to currency at 2 decimal places.
It is not totaling correctly. I am assuming it may be a rounding problem, but don't see where the culprit is.