IIF statement error

[cookie5]

I had the following query sql working correctly:

SELECT Workorders.WorkorderID, Workorders.[Date:], Workorders.[Customer:], Workorders.[Signal Locations:], Workorders.[School Flashers:], Workorders.[Flasher Locations:],

Sum(([workorders empdetails]![Emp 1 Hours:]*[Employees]![PayRatew/Benefits/IIf([SBL],2,1)) AS [Totalreg Labor],

Workorders.[Time Arrived:], Workorders.[Time Completed:], CInt(CDbl(Nz([Time Arrived:])*24*60)) AS minin, CInt(CDbl(Nz([Time Completed:])*24*60)) AS minout, [minout]-[minin] AS totmin, IIf([minin]=0 Or [minout]=0,[totmin]=0,[totmin]) AS minutes, IIf([minutes]<0,[minutes]=0,[minutes]) AS allmin, ([allmin]/60) AS hours,

Sum(([workorders empdetails]![Emp 1 OT]*[Employees]![OvertimeRatew/Benefits])/IIf([SBL],2,1)) AS [Totalot Labor], [Totalreg Labor]+[Totalot Labor] AS [Total Labor],

Workorders.[Work Activity], FROM (((((Employees INNER JOIN (Workorders RIGHT JOIN [workorders empdetails] ON Workorders.WorkorderID = [workorders empdetails].WorkorderID) ON Employees.EmployeeID = [workorders empdetails].[Employee - 1:])
GROUP BY Workorders.WorkorderID, Workorders.[Date:], Workorders.[Customer:], Workorders.[Signal Locations:], Workorders.[School Flashers:], Workorders.[Flasher Locations:],Workorders.[Time Arrived:], Workorders.[Time Completed:], Workorders.[Work Activity],

The Sum statements are the ones that I had to add additional conditions to checking for checkboxes RTA, RTB, RTC, RTD, RTE. If one is checked I want to multiply by the percentage from previous query fields RTAper or RTBper or RTCper or RTDper or RTEper according to which check box is checked, so now my sql is

SELECT Workorders.WorkorderID, Workorders.[Date:], Workorders.[Customer:], Workorders.[Signal Locations:], Workorders.[School Flashers:], Workorders.[Flasher Locations:], Workorders.RTA, RTAper.RTAper, RTAper.FirstOfRTACUST,

Sum(([workorders empdetails]![Emp 1 Hours:]*[Employees]![PayRatew/Benefits])*IIf([RTA],[RTAper],1)*IIf([RTB],[RTBper],1)*IIf([RTC],[RTCper],1)*IIf([RTD],[RTDper],1)*IIf([RTE],[RTEper],1)/IIf([SBL],2,1)) AS [Totalreg Labor],

Workorders.[Time Arrived:], Workorders.[Time Completed:], CInt(CDbl(Nz([Time Arrived:])*24*60)) AS minin, CInt(CDbl(Nz([Time Completed:])*24*60)) AS minout, [minout]-[minin] AS totmin, IIf([minin]=0 Or [minout]=0,[totmin]=0,[totmin]) AS minutes, IIf([minutes]<0,[minutes]=0,[minutes]) AS allmin, ([allmin]/60) AS hours,

Sum(([workorders empdetails]![Emp 1 OT]*[Employees]![OvertimeRatew/Benefits])*IIf([RTA],[RTAper],1)*IIf([RTB],[RTBper],1)*IIf([RTC],[RTCper],1)*IIf([RTD],[RTDper],1)*IIf([RTE],[RTEper],1)/IIf([SBL],2,1)) AS [Totalot Labor],

[Totalreg Labor]+[Totalot Labor] AS [Total Labor],
Workorders.[Work Activity], Workorders.RTB, Workorders.RTC, Workorders.RTD, Workorders.RTE, RTBper.RTBper, RTCper.RTCper, RTDper.RTDper, RTEper.RTEper
FROM (((((Employees INNER JOIN (Workorders RIGHT JOIN [workorders empdetails] ON Workorders.WorkorderID = [workorders empdetails].WorkorderID) ON Employees.EmployeeID = [workorders empdetails].[Employee - 1:]) LEFT JOIN RTAper ON Workorders.[Customer:] = RTAper.FirstOfRTACUST) LEFT JOIN RTBper ON Workorders.[Customer:] = RTBper.FirstOfRTBCUST) LEFT JOIN RTCper ON Workorders.[Customer:] = RTCper.FirstOfRTCCUST) LEFT JOIN RTEper ON Workorders.[Customer:] = RTEper.FirstOfRTECUST) LEFT JOIN RTDper ON Workorders.[Customer:] = RTDper.FirstOfRTDCUST
GROUP BY Workorders.WorkorderID, Workorders.[Date:], Workorders.[Customer:], Workorders.[Signal Locations:], Workorders.[School Flashers:], Workorders.[Flasher Locations:], Workorders.RTA, RTAper.RTAper, RTAper.FirstOfRTACUST, Workorders.[Time Arrived:], Workorders.[Time Completed:], Workorders.[Work Activity], Workorders.RTB, Workorders.RTC, Workorders.RTD, Workorders.RTE, RTBper.RTBper, RTCper.RTCper, RTDper.RTDper, RTEper.RTEper
HAVING (((Workorders.RTA)=True)) OR (((Workorders.RTB)=True)) OR (((Workorders.RTC)=True)) OR (((Workorders.RTD)=True)) OR (((Workorders.RTE)=True));

When I run the query with one of the checkboxes checked, it does not multiply by the per(centage) for that checkbox. In fact, it leaves the fields for that per(centage) blank as well as Totalreg Labor and Totalot Labor. If I code in to multiply by a number like 2 and not the per(centage) field, it does fine...What am I missing?
Any help would be greatly appreciated.....again.

 

[lameid]

On the surface nothing seems amiss in your SQL to me. This makes me wonder about data...

Do you have any nulls in your Yes/NO fields? If so get rid of them.

Otherwise I recomend for testing purposes going back to a non aggregate query (simple select) and adding firelds for each IIF that calculates the percentage and look for bad results in those individual expressions and try to figure out what is wrong.

Also instead of the following expression

IIf([RTD],[RTDper],1)

You might try

IIf([RTD] = Yes,[RTDper],1)

That might give you better results if Nulls are involved and obviously you would have to fix all similar expressions.