Access Calculation

[mot98]

Hi all,

I have an access query that is totaling hours worked grouped by day. I need to give a "Regular Hours Total" and an "Overtime Hours Total". I am trying to use the following syntax, but keep getting errors.


If Sum([Total Hours]) > 8 Then =8 Else =Sum([Total Hours])


Then for to calculate the overtime hours I am using this


If Sum([Total Hours]) > 8 Then =(Sum([Total Hours]) -8) Else = 0


Can someone tell me the correct syntax to get this to work.

Thanks,


mot98

"I'd rather be dead and cool..then alive and uncool."
---Harley Davidson & The Marlboro Man

 

[mot98]

Never mind...got it figured out.

Thanks...

mot98

"I'd rather be dead and cool..then alive and uncool."
---Harley Davidson & The Marlboro Man

 

[funger]

This should do it for you.... to use a conditional statement in a query use the iif() function. More information can be found in help.

SELECT tblHours.WorkerID, IIf(Sum([TotalHours])>8,8,Sum([TotalHours])) AS Normal, IIf(Sum([TotalHours])>8,Sum([TotalHours])-1,0) AS Overtime
FROM tblHours
GROUP BY tblHours.WorkerID;

Hope that helps!
-Jim

 

[funger]

guess I'm not quick enough. Glad you figured it out though!