Time sums in Access

I’m sure someone out there has the answer to this! Don’t be shy!!

I need to calculate the difference between two times [Start] and [Finish], and sum several records. It’s a time sheet. The problem I have is concerned with the way Access stores dates.

I am using a midnight operator and after several formats have come up with a control [Total] that uses as source =([Start]-1)-([Finish-1]). This handles midnight OK.

Then I have to convert these times to a general number (or Fixed, it don’t matter) so I can add them up. If you stay with Time, once your employee totals go over 24 hours, Access returns 0 (re-sets the clock). Well, it would, it’s a 24 hour clock!

My control [TotalControl] picks up each [Total] and converts it. No problem for times before midnight, but once you go over midnight in a [Total] calculation, the result is unexpected.

For example, employee Start = 22:00, Finish =08:00. My math in [Total] actually does the equivalent of 24:00 (midnight – yes I know the clock would read 00:00) – 22:00 = 02:00 + 08:00 = 10:00 – the correct amount of hours worked. But the result in [TotalControl] is 14:00, which is actually 24:00 – 22:00 = 02:00 – (24:00 – 08:00)

I have tried all sorts including adjusting the calculation so the times are compared to 23:59, but I always get the same result.

[Total] is fine, but what can I do to make the summing of these work??

I really need some help please!! It has to be solvable, cos Access must be used as a time billing tool. – I can’t alter the format of [Start] and [Finish] to number (eg .5 = ½ hour as the base also prints work rotas, so has to display time.

Thanks for reading.