Access Query with IF THEN statements

Question posted by: berio211 (Newbie) on July 24th, 2008 03:30 PM

What I am trying to do is create a query in Access that will help in my Worker's Comp reporting. I already have queries set up but I am missing one component. I have a query that states IF payrollold is greater than 750 THEN 750 IF not THEN payrollold. However I need to add a twist in that I need to add a statement which would give me the following result: IF hours is greater then 80 THEN 1500 (in the payroll column) IF not then 750.

I am having trouble mixing the hours part with the payroll part. Below is the SQL view of the query I need to modify.

SELECT ADPINTER.JOB_NUMBER, ADPINTER.EMPNO, ADPINTER.[EMPLOYEE NAME], [reg_hrs]+[ovr_hrs]+[other_hours] AS hours, EMPMAST.RATE1AMT, [hours]*[rate1amt] AS payrollold, EMPMAST.WORKERSCOMPCODE, ADPINTER.ENDPERIOD, 1 AS [COUNT], ADPINTER.[UNION], IIf([payrollold]>750,750,[payrollold]) AS Payroll, ADPINTER.[total pay], ADPINTER.category, [Reg_grs]+[ovr_grs]+[Other Gross] AS gross
FROM EMPMAST INNER JOIN ADPINTER ON (EMPMAST.[CO CODE] = ADPINTER.COMPANY) AND (EMPMAST.EMPNO = ADPINTER.EMPNO) AND (EMPMAST.EMPNO = ADPINTER.EMPNO)
WHERE (((ADPINTER.JOB_NUMBER) Between [forms]![autoload]![From Job] And [forms]![autoload]![To Job]) AND ((ADPINTER.ENDPERIOD) Between [forms]![autoload]![STARTDATE] And [forms]![autoload]![ENDINGDATE]) AND ((ADPINTER.category)<>"GC")

Any help would be greatly appreciated. Thanks Lou

Not the answer you were looking for? Post your question . . .
189,798 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).