Unanswered: IIf Statement to calculate overtime hours.
I am looking for help with an IIf statement. I am trying to set up a database to track project work. Part of this is to keep track of field tech hours worked. Overtime has to be calculated on a daily basis. There are several work codes: Travel In (or AirTravel In), Site, Shop, StandBy, Travel Out (or AirTravel Out). I would like to enter the total time for each work code and set up a query to calculate the regular and overtime hours. I can't seem to figure this out. The hours worked need to be figured out in the order listed above – the first 8 hours worked will be Standard and the remaining hours of the day will be Premium. The work codes must be kept separate as they are charged out at different rates. I can use the statements below (at least they seem to work) to divide up the STI (Standard Travel In) hours and the PTI (Premium Travel In) hours:
STI: IIf([Travel In]<=8,([Travel In]),8)
PTI: IIf([Travel In]>8,([Travel In]-8),0)
Next the Site hours would be calculated, if there are any hours traveled in they would be counted before site hours. Say a tech traveled in 6 hours on Monday, worked 5 hours on Site and then traveled 6 hours out. I want to enter the time as stated and have Access calculate out the time as: 6 STI, 2 SS (standard site), 3 PS (premium site) and 6 PTO (premium travel out). Does anyone know how the Site hours statement would be written, or where there might be a sample I could look at, or if this is even possible?
I would suggest doing this in VBA code and looping through the entries in order by time and generating your totals for sti, ss, pt, and pso. When the day changes, write the record then zero out your totals again.
Thanks for the reply but that is all Greek to me. Can't find an answer to this so have decided to enter it in separate fields for now. I have been away from working in Access for a few years now and I was never even close to being an expert. Have created simple databases for others to use mostly for data entry and creating mailing labels and such. Have written macros but know nothing about modules. I will keep it simple for now and try and hit the books and pick up where I left off. Thanks everyone!