Calculating Work Hours Overlap

[DB_Queen]

Hello experts:

I have a report I'm creating that calculates the overlap between an employee's schedule and their supervisor.

I have the report grouped by supervisor in a group header and the employee info in a detail section. I'm using VBA to calculate the overlap on the FORMAT event of the detail section, in this way:

If SuperStartTime > EmployeeStartTime Then
Monday1StartTIme = Nz((DateDiff "h", EmployeeStartTime,SuperStartTime),0)
Else
Monday1StartTime = Nz((DateDiff "h", SuperStartTime,EmployeeStartTime),0)
End If

I've included the Nz because sometimes employees or supervisors work a 4/10 schedule where one day of the week has no start or end time. The difference found between the times is stored in a variable, for example here "Monday1StartTime" which is set as DOUBLE.

At the end, all the variables are added up, and subtracted from 80 hours, to get the total hours of overlap.

This calculation works great for about 95% of records; the issue is randomly some employees will display a huge negative number like -87564 instead of "80" or "50" or whatever. The weirdest part is that when I amend the query for the report to pull only that particular employee's record, the calculation will work fine; but when I pull all employee records into the report it will not calculate correctly!

Have any of you run up against something like this before? I am not able to find any obvious similarities between the records where this miscalculation is occurring; however universally if I run the query for ONLY THAT EMPLOYEE the math works fine.

Appreciate any guidance you can provide. Thanks

 

[Ranman256]

check the fields for null before the calc:

if Isnull(superstarttime) or IsNull(EmpStarttime) then
   vStartTime = 0
else
  vStartTime = DateDiff("h",superstarttime, EmpStartTime)
endif

 

[DB_Queen]

Thank you, but that won't work. I have days where the supervisor may be out, but the employee is in. The variable can't be zero in this case, because there are 8 hours in that day when the employee does not have a supervisor present (there is no overlap that day). Hope that makes sense.

 

[The_Doc_Man]

The number -87654, if that is actually what you saw and not just an offhanded number, if treated as a number of hours, is something bizarre like almost exactly 10 years. I'm with Ranman256 on this one - there is possibly a null sneaking in that is causing the math to get "funny" on you. By any chance do you have 10 employees involved in this computation?

 

[DB_Queen]

The number -87654, if that is actually what you saw and not just an offhanded number, if treated as a number of hours, is something bizarre like almost exactly 10 years. I'm with Ranman256 on this one - there is possibly a null sneaking in that is causing the math to get "funny" on you. By any chance do you have 10 employees involved in this computation?

There are 300 employees in the table. That number is just an offhand number I typed as an example What is really boiling my noodle is the fact that if I run just a single employee, the math works; if I pull all records, that same employee shows a huge negative number.

 

[DB_Queen]

Here's screen shots, so you can see what I'm referencing. Shot #1 is the whole team; shot#2 is the single employee. You can see the math works fine when the query is drilled down to one specific record. Hope this helps clarify what I'm up against