Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I deduct for lunch time?

Status
Not open for further replies.
Oct 8, 2003
15
US
Hello,

I've built a database to keep track of payroll information and I need to have it automatically deduct a half an hour if the person works more than six hours. The complicating factor is that I only need the deduction if the person is in a certain department and worked a certain project.

Example: John works 7 hours in box office for general hours. (It's possible for John to work in box office for a specific show as well.

I've tried the IIF statement:
IIf([RegHours]>6 And [ProjID]="Pbox",([RegHours]-0.5),[RegHours])
in the Criteria row of a query and it doesn't work. It does work as a column but I only need it to change the hours for people that work a "Pbox" event, not everyone.

Suggestions?
 
When you say "it doesn't work" ... what does that mean exactly. No values? Wrong Values? Errors out?

You don't in any event, want the IIF in the criteria row, you want it as a field something like

Hours: IIf([RegHours]>6 And [ProjID]="Pbox",([RegHours]-0.5),[RegHours])
 
I should have mentioned this before... frustration got the best of me.

When the IIf statement is in the criteria row, it removes the records that need a half an hour subtracted completely rather than just subtracting the half an hour. My logic in using the criteria row was that I could use this for multiple project ID's. As a column, the multiple project IIf statement looks as follows:

IIf([RegHours]>6 And [ProjID]="Pbox" OR "PCON" OR "PFRONT",([RegHours]-0.5),[RegHours])

is what I have tried in the column... using the OR statements, a half an hour is deducted from all entries, not just those equal to [ProjID].

That's what not working means...
 
I suspect that the problem with your IIF is that you need to explicitly include the comparison for each value like this.
Code:
    IIf([RegHours]>6 And 
        ([ProjID]="Pbox" OR 
         [ProjID]="PCON" OR 
         [ProjID]="PFRONT"),([RegHours]-0.5),[RegHours])
The way you have it, it is evaluating "PCON" and "PFRONT" as booleans by casting them to numbers and concluding that they are TRUE (i.e. not zero) ... consequently the whole expression is TRUE because of the OR separators and 1/2 hour is subtracted for every record.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top