How to use the result of a check box in a calculation in a query

[cookie5]

Hi,
I have a form with a checkbox. The checkbox field is also a field in a table. I want to run a query that if the checkbox is marked will perform a calculation. (It will take the amount of labor and divide it in half.) Any ideas?
Thanks in advance.

 

[PHV]

What have you tried so far ? Where are you stuck ?
The "result" of a check box is simply a Boolean value ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cookie5]

Currently the sql for my query is:
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], [Totalreg Labor]+[Totalot Labor] AS [Total Labor], Workorders.[Work Activity], IIf([Workorders].[BCBOE]=Yes,[Total Labor]/2) AS [Total Labor2]
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 area in question is the IIF for Total Labor2
The error I am getting is
You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)
You tried to execute a query that does not include the specified expression as part of an aggregate function or grouping.

Possible cause:

You did not enter an aggregate function in the TRANSFORM statement.
Thanks

 

[cookie5]

My problem is that I do not know what the error message means.Please help me understand.
Thanks

 

[PHV]

You may try to replace this:
IIf([Workorders].[BCBOE]=Yes,[Total Labor]/2) AS [Total Labor2]
With this:
Sum(IIf([Workorders].[BCBOE]=True,Nz([workorders empdetails].[Emp 1 Hours:]*[Employees].[PayRatew/Benefits],0)+Nz([workorders empdetails].[Emp 1 OT]*[Employees].[OvertimeRatew/Benefits],0),0)/2 AS [Total Labor2]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cookie5]

thanks PH,
That worked great...YOU ARE THE MASTER !!!!

 

[lespaul]

here's a thread, thread701-1258387, that has an explanation of the GROUP BY clause, that was the actual source of your error message. Any fields in the SELECT that are not aggregates (SUM, AVG, MIN, MAX, COUNT, etc) HAVE to be listed in the GROUP BY clause or you get the errors you listed above.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins