Need help with a nested IIf in a query (Access 2007)

[Ceejay64]

I have a table in which we put clock in and clock out times for employees that tracks weekend coverage (plus all other relevent ee info). I have done a query that calculates the hours the ee is at work, which is functioning just fine, but in order to properly calculate "paid hours" I need to add .5 hrs to anyone who works a full 3rd shift as they only work 7.5 hrs, but get paid 8.

Initially, I wrote a single IIf statement to add the time for 3rd shifters, but this can only apply if they work a full shift, which not all do, so I need to specify that the calculation should only apply to anyone whose time calculates initially to the 7.5 hrs. But it doesn't work.
In the query, "Shift" is the shift, and "Hours" is the calculated amount of time. I created another field in the query called "hrs" and put in an expression. Here is what I have that isn't working right:

IIf([shift]="3",IIf([hours]="7.5",([hours]+0.5),[hours]))

It works fine for anyone on any other shift, but I get #error in all 3rd shift rows. When I remove the 2nd IIf, it adds .5 to all 3rd shift entries, which isn't always needed.

Any advice would be welcome! Thanks so much!

 

[namliam]

assuming both shift and hours is a number fields:
IIf([shift]=3,IIf([hours]=7.5,([hours]+0.5),[hours]))

Should do the trick... Also query: What if they work 7.75 hours? or 8.5 or 10 hours?

 

[Ceejay64]

Anything other than 7.5 is paid clock. Confusing, I know.

Thanks for the help, namliam. I copied and pasted the formula you provided, but now I'm getting "#error" in every row, regardless of shift.

I'm gonna play with it a little more... there may be some other way to capture this anomaly.

 

[namliam]

Is shift and/or hours a number or text string??
Guess work....
IIf([shift]="3",IIf([hours]>=7.5,([hours]+0.5),[hours]))

 

[Ceejay64]

Is shift and/or hours a number or text string??
Guess work....
IIf([shift]="3",IIf([hours]>=7.5,([hours]+0.5),[hours]))

both are numbers

 

[namliam]

IIf([shift]="3"
, IIf([hours]="7.5"
 , [hours]+0.5
 , [hours])
, [hours])

This should work....

 

[Ceejay64]

Thanks, namliaM! Just what i needed! You Rock!

 

[Rx_]

F.Y.I.
When there is a business rule that might be either updated later or used agan, it might be worth looking at creating a Function in your Modules.

Then, by calling the function and putting the field names in the arugments, you can get the same result.

Some of the advantages of doing this are:
1. Comments! Things change so comments help to keep track of things.
2. If this is being used in multiple places, there is one place to update it as change come into play.
3. Error Code Handling - incase there are some pesky nulls or someone enters a number of hours greater than is possible (i.e. 2,000 hours for one week of a pay check).
4. Code Reuse For some formulas that can be used in your next project, a module is easy to copy and transfer.