Newbie Help

[JEA]

I'm new to Access, so please be patient.

I'm trying to write an expresion that will work out how many hours were missed from a given shift, using the start and finish times, how long the shift was ment to be, and how many staff showed up compared to how many were ment to.

The expression is getting hugely complicated to type in a text fiel on a form, there must be an easier way, please help.

I'm familiar with java but not VBA or SQL. I'm not sure if an expresion is the best way to tackle this, should I use the VBAEditor or a macro? If so how?

My expression so far (it's laid out like this in the hope of making it easier to understand):

=IIf(
[StaffReq]>=4 And IsNull([Staff4PIN]), //If 4 staff are req but there's no entry for the 4th member of staff...
DLookUp("[ShiftLength]","WorkedShiftTypeLookUp","[S4Type]=[ShiftType]"), //... then the whole shift is missed, get shift length form lookup table.
DLookUp("[ShiftLength]","WorkedShiftTypeLookUp","[S4Type]=[ShiftType]") //else, work out missed hours (shift length minus worked hours) Get Shift length
- //Minus
IIf( //Work out number of hours worked
[S4Type]="Day", //If it's a day shift...
DateDiff("h",[Staff4StartTime],[Staff4FinishTime]),//...then start time and finish time will be on the same day, so work out the difference between the two
IIf( // else it's a night shift, start time will be larger than finish time so DateDiff() won't know it's over two days and will return -ve no.
[Staff4FinishTime]<12:00,//If finish time is less than 12:00, the shift must have finished the day after it started, therefore work out the hours worked before 00:00 on the first day added to the hours worked after 00:00 on the second day.
DLookUp("[ShiftLength]","WorkedShiftTypeLookUp","[S4Type]=[ShiftType]")//Find out how many ours were ment to be worked, ready to minus the worked hours from it.
-minus
( DateDiff("h", "00:00", [Staff4FinishTime]) + DateDiff("h",[Staff1StartTime],"00:00")) ,//Add the time worked before 0000 to the time worked after 0000
DLookUp("[ShiftLength]","WorkedShiftTypeLookUp","[S4Type]=[ShiftType]")-DateDiff("h",[Staff4FinishTime],[Staff4StartTime])//If finish time is greter than 12:00, the shift finished on the same day it started, so normal shift lenght minus DateDiff() calc will work.
)
)
)

 

[Ripley]

Ok start from the beginning. The best way to do this would be to write it in VBA code.

Itemise exactly what you need to expression to do in simple english...

 

[JEA]

sorry,

I have a table that records the number of staff required per day, each staff member's ID, start and finish time (just time, not time and date), and the shift type. It is indexed by the date the shift was started.

There is a seperate shift-type look-up table that stores each shift type and it's length. eg) a normal day shift is 12hrs long and a normal night shift is 12 hrs long. This is so I can add different shift types later.

When I enter the days shifts on a form, I want a text box to display how many hours went unworked that should have been worked.
If not enough staff members turned up, it's the full shift length. For staff that turned up it's the difference between the number of hours worked and the shift length.
A night shift runs from 1830 to 0630 the next day, which is making working out the number of hours worked difficult.


I'm going to use this calculation alot. On Reports as well as on this form. I will eventually (hopefully) use this result as criteria for reports aswell. Eg) show all days with more than x missed hours etc.

 

[JEA]

Solved it (I think) using a very helpful tutorial http://www.fontstuff.com/vba/vbatut01.htm


Function MissedHours(StartTime As Date, EndTime As Date, ShiftLength As Integer)

Dim Hbefore12 As Date 'Declare variables
Dim Hafter12 As Date
Dim HoursWorked As Integer


If StartTime > EndTime Then 'If StarTime>EndTime it's a night shift over 2 days
Hbefore12 = DateDiff("h", StartTime, #12:00:00 AM#) 'Hours worked before 0000 on 1st day
Hafter12 = DateDiff("h", #12:00:00 AM#, FinishTime) 'Hours worked After 0000 on 2nd day

HoursWorked = Hbefore12 + Hafter12 'Total hours worked over night shift

MissedHours = ShiftLength - HoursWorked 'Return missed hours

Else 'If StartTime<EndTime the shift was over one day
HoursWorked = DateDiff("h", StartTime, EndTime) 'Total Hours Worked

MissedHours = ShiftLength - HoursWorked 'Return missed hours

End Function

 

[Ripley]

indeed, thats the stuff sorry for late reply i had to goto work!