IIF statement error

[cookie5]

I have the following sql statement in a query;
Sum(IIf([Workorders].[BCBOE]=True,([Totalreg Labor])+([Totalot Labor])/2), Sum(([Totalreg Labor])+([Totalot Labor])) AS [Total Labor])

I am getting the error Syntac error (missing operator) in query expression.
It highlights the AS. I can't find my error, can anyone help?
thanks again in advance.

 

[SkipVought]

Hi,

A SUM within a SUM and NOT?

Take the INTERNAL sum out and check your parentheses.

Skip,

_[red][/red]

 

[cookie5]

Skip,
I took out the internal sum but still got the same error message. The parentheses left and right are equal. What I am trying to do is to take the result of the BCBOE, if true sum the 2 labor amounts and divide by 2, if it is false just add the 2 labor amounts, either way the result needs to go into Total Labor.
Any other suggestions?

 

[SkipVought]

no parenthesis after AS [Total Labor]

Skip,

_[red][/red]

 

[cookie5]

Skip,
I removed the paren after AS[Total Labor] but got the same error. The statement now reads.
Sum(IIf([Workorders].[BCBOE]=True,([Totalreg Labor])+([Totalot Labor])/2), (([Totalreg Labor])+([Totalot Labor])) AS [Total Labor]
thanks for any help

 

[dhookom]

Try:
Sum(IIf([Workorders].[BCBOE]=True,[Totalreg Labor]+[Totalot Labor]/2, [Totalreg Labor]+[Totalot Labor])) AS [Total Labor]

From you expression, it isn't clear what should be divided by 2.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cookie5]

I want to divide the sum of Totalreg Labor and Totalot Labor by 2 if the BCBOE field =True otherwise I just want to sum Totalreg Labor and Totalot Labor.
Thanks for any help you can give me

 

[dhookom]

Try:
Sum(IIf([Workorders].[BCBOE]=True,([Totalreg Labor]+[Totalot Labor])/2, [Totalreg Labor]+[Totalot Labor])) AS [Total Labor]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cookie5]

I get the error "subqueries can not be used in the expression" Here is the entire query.

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]) 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]) AS [Totalot Labor],
Sum(IIf([Workorders].[BCBOE]=True,([Totalreg Labor]+[Totalot Labor])/2, [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];

Thanks

 

[dhookom]

I don't know which issue to address first. Every field or expression in your SELECT clause must either be included in the GROUP BY clause or must be an aggregate like Sum() or Avg() or Count().

Also, what are you trying to do with:
IIf([minin]=0 Or [minout]=0,[red][totmin]=0[/red],[totmin]) AS minutes



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]