Query Expression Help

[all7holly]

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. The same goes for the ProjectManager and AMS positions. Any ideas? Your help would be greatly appreciated.

Thanks,
Holly

 

[plog]

I think you need to create a custom function in a module instead of trying to force it all into one expression. That would make your code a lot easier to follow.

Also, I'm pretty sure your data isn't structured properly. Instead of a field for each type of hours, you should have a table that holds the hours as well as the type of hours they are. From there you could link to a rate table and determine the rate for each type. The way you are doing it now by hard coding the rates into the query is going to make it difficult to update the rates in the future.

Last, whenever you have a problem with something not returning what you expect please always post what data you are feeding it, what data it is returning and what data you expect it to return.

 

[all7holly]

I do have a table that holds the hours and type of hours. If I did have a module for this; what would that look like?

 

[plog]

If would simply be a function that you pass all the data fields you use in calculating TotalRatePositions. You would then essentially rewrite that one Iif statement into multiple lines so its easier to follow and know what is happening in each step of the process.

 

[all7holly]

Maybe this would make more sense?
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))))
I keep getting a coma error when I run the query.

 

[plog]

That's because you've crammed all your logic into one line. You need to build a custom VBA function that can methodically manipulate your data.

Search this site or google for Access VBA tutorials.

 

[vbaInet]

Right Holly, I can already see some problems with your IIF() statement. You haven't written the False part in some of them.

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

Also, because you have quite a lot of calculated fields, I would advise that you remove TotalRateAllPositions from the query and save it. Create a new query and base it on your original query, include all the fields from the orginal query then add TotalRateAllPositions as a calculated field in this new query.

I noticed that all you've done is repeat the calculations in TotalRateAllPositions and not referred to the aliases. But since you've now pulled the calculated fields into this new query, refer to those fields in your calculation. You will also need to re-think your logic.