Query Expression Help

[hmartin]

Below is the expressions broken down into 1 column each.

CABIHours: IIf([work hours]![work code]=60,[hours])
CABIOTHours: [CABIHours]-8

TotalCABIRate: IIf([CABIHours]>8,(500+([CABIHours]-8)*93.75),[CABIHours]*62.5)

ProjectManagerHours: IIf([work hours]![work code]=14,[hours])

ProjectManagerOTHours: [ProjectManagerHours]-8

TotalProjectManagerRate: IIf([ProjectManagerHours]>8,(576+([ProjectManagerHours]-8)*95),[ProjectManagerHours]*72)

AMSHours: IIf([work hours]![work code]=62,[hours])

AMSOTHours: [AMSHours]-8


TotalAMSRate: IIf([AMSHours]>8,(576+([AMSHours]-8)*95),[AMSHours]*72)

I need these expressions to be on one line. Below is what I thought the correct expression would be.

TotalRateAllPositions: (IIf([work hours]![work code]=60,[hours]),IIf([CABIHours]-8), IIf([CABIHours]>8,(500+([CABIHours]-8)*93.75),[CABIHours]*62.5), IIf([work hours]![work code]=14,[hours]),Iff([ProjectManagerHours]-8), IIf([ProjectManagerHours]>8,(576+([ProjectManagerHours]-8)*95),[ProjectManagerHours]*72), IIf([work hours]![work code]=62,[hours]),(IIF[AMSHours]-8), IIf([AMSHours]>8,(576+([AMSHours]-8)*95),[AMSHours]*72)))))))))

I need this expression to calculate the normal hours for the CABI position, then calculate the overtime hours, then the rates for normal time and overtime. As you can see the CABI rates are different from the ProjectManager and AMS positions. The same goes for the ProjectManager and AMS positions. Any ideas? Your help would be greatly appreciated.

Thanks,
Holly

[hmartin]

I should have posted this expression. I get a comma error when I try to run it.

TotalRateAllPositions: (IIf([CABIHours]>8,(500+([CABIHours]-8)*93.75),[CABIHours]*62.5),IIf([ProjectManagerHours]>8,(576+([ProjectManagerHours]-8)*95),[ProjectManagerHours]*72),IIf([AMSHours]>8,(576+([AMSHours]-8)*95),[AMSHours]*72))

[ssanfu]

I should have posted this expression. I get a comma error when I try to run it.

TotalRateAllPositions: (IIf([CABIHours]>8,(500+([CABIHours]-8)*93.75),[CABIHours]*62.5),IIf([ProjectManagerHours]>8,(576+([ProjectManagerHours]-8)*95),[ProjectManagerHours]*72),IIf([AMSHours]>8,(576+([AMSHours]-8)*95),[AMSHours]*72))

From your first post, these three expressions have improper syntax:

CABIHours: IIf([work hours]![work code]=60,[hours])
ProjectManagerHours: IIf([work hours]![work code]=14,[hours])
AMSHours: IIf([work hours]![work code]=62,[hours])

The syntax is:

IIF(condition, TRUE clause, FALSE clause)

You are missing the FALSE clause. It is not optional.
Then when you use them in the TotalRateAllPositions expression, you are receiving an error. There are too many close parentheses and not enough commas.

You have a 9 level nested IIF() function. That is a lot. It might be better to write a UDF.

You can consolidate conditions... I would start out with the skeleton

IIF(condition, TRUE clause, FALSE clause)

add the conditions:

IIF([work code]=60 or [work code]=14 or[work code]=62, TRUE clause, FALSE clause)

then add the TRUE clause:

IIF([work code]=60 or [work code]=14 or[work code]=62, [hours], FALSE clause)

You are going to have another IIF() statement, so add that as the FALSE clause:

IIF([work code]=60 or [work code]=14 or[work code]=62, [hours], IIF(condition, TRUE clause, FALSE clause))

Add another condition (in blue) for the 2nd IIF() function, add the TRUE clause, then add another IIF() as the FALSE clause. Continue building the expression until you a) are done or b) reach the max nesting limit.

[hmartin]

First of all thank you very much for you help. I was trying to use this expression in a query. I apologize but I don’t know how to write this as a code at all. I’m not sure what the FALSE clause would be. I would really appreciate your help. This is going to generate billing for a large job we were just awarded. I need to rate for each position worked each day.
8 hours will be billed at $62.50 an hour for the CABI position

Overtime anything over 8 hours in a day will be billed at $93.75 an hour for the CABI position

8 hours will be billed at $72.00 an hour for the ProjectManager position

Overtime anything over 8 hours in a day will be billed at $95.00 an hour for the ProjectManager position

8 hours will be billed at $72.00 an hour for the AMS position

Overtime anything over 8 hours in a day will be billed at $95.00 an hour for the AMS position

[ssanfu]

First of all thank you very much for you help. I was trying to use this expression in a query. I apologize but I don’t know how to write this as a code at all. I’m not sure what the FALSE clause would be. I would really appreciate your help. This is going to generate billing for a large job we were just awarded. I need to rate for each position worked each day.
8 hours will be billed at $62.50 an hour for the CABI position

Overtime anything over 8 hours in a day will be billed at $93.75 an hour for the CABI position

8 hours will be billed at $72.00 an hour for the ProjectManager position

Overtime anything over 8 hours in a day will be billed at $95.00 an hour for the ProjectManager position

8 hours will be billed at $72.00 an hour for the AMS position


Overtime anything over 8 hours in a day will be billed at $95.00 an hour for the AMS position

So the field [work code] differentiates which position?
Is the following correct?

[work code]=60 CABI
[work code]=14 ProjectManager
[work code]=62 AMS

Are the hourly rates for ST & OT available in the query? If so, what are the field names? (I am trying not to hard code the rates.)

Would you post the SQL of the query?

[hmartin]

Hello,

Yes the work codes are correct. The ST and OT rates do not have field names. I only calculated the hours and not the rates. Below is the SQL. Again I really appreciate your help.
SELECT IIf([work hours]![work code]=60,[hours]) AS CABIHours, [CABIHours]-8 AS CABIOTHours, IIf([CABIHours]>8,(500+([CABIHours]-8)*93.75),[CABIHours]*62.5) AS TotalCABIRate, IIf([work hours]![work code]=14,[hours]) AS ProjectManagerHours, [ProjectManagerHours]-8 AS ProjectManagerOTHours, IIf([ProjectManagerHours]>8,(576+([ProjectManagerHours]-8)*95),[ProjectManagerHours]*72) AS TotalProjectManagerRate, IIf([work hours]![work code]=62,[hours]) AS AMSHours, [AMSHours]-8 AS AMSOTHours, IIf([AMSHours]>8,(576+([AMSHours]-8)*95),[AMSHours]*72) AS TotalAMSRate, [Work Hours].ID, [Work Hours].Project, [Work Hours].Employee, [Work Hours].[Date Worked], [Work Hours].TimeIn, [Work Hours].TimeOut, [Work Hours].[Work Code], [Work Hours].Hours, [Work Hours].Notes, [Work Hours].Mileage, [Work Hours].[Equipment Used], [Work Hours].[Equipment Qty], [Work Hours].EquipmentRate, [Work Hours].[Per Diem Applies], [Work Hours].[Payroll Notes Date], [Work Codes].Description, [tbl Work Auth].ProjectNumber, [tbl Work Auth].*, tblProjectEquipment.*, [Project Expenses].*
FROM ([Project Expenses] LEFT JOIN ([tbl Work Auth] LEFT JOIN ([Work Codes] RIGHT JOIN [Work Hours] ON [Work Codes].ID = [Work Hours].[Work Code]) ON [tbl Work Auth].ProjectNumber = [Work Hours].Project) ON [Project Expenses].ProjectNumberEX = [tbl Work Auth].ProjectNumber) LEFT JOIN tblProjectEquipment ON [tbl Work Auth].ProjectNumber = tblProjectEquipment.ProjectNumberEQU
WHERE ((([tbl Work Auth].ProjectNumber)="012004"))
ORDER BY [Work Hours].[Date Worked];

[ssanfu]

In looking at your query, it looks like there can be only one [work code] per record, so I didn't calculate the rates for all 3 positions per record.

I created a query as a test and attached the SQL in a text file.
Create a new query and paste in the SQL from the attachment "SS_Query.txt". This is to see if I am close on what you are trying to calculate.

Then I merged part of my query with the SQL you posted. Create another new query and paste on the SQL from "hm_query.txt". Hopefully this will return the correct values.


---
I would advise you to stop using spaces in object names. It will eventually cause you headaches.

[hmartin]

Oh my goodness! You did it! I cant thank you enough. You have been a huge help. May I pick you brain form time to time? Clearly you are an access expert! Thank you so much. It is greatly appreciated.

Kind Regards,
Holly