Total hours calculation

[Newbe]

Need to calculate total hours on a form, based on two
fields one field [Start]and other field [End]both are
formatted for Short time. Made Text box with the Control
Source = ([End]-[Start])*24. Worked fine until I needed to
input work from second shift with a "Start" time of 23:30
and an "End" time of 3:00 this ended up giving a "Total"
time of -20.5 instead of the expected 3.5 hours.
Not real proficient at VBA yet, is there an easy way to
remedy this problem.

TIA

 

[John Vinson]

Need to calculate total hours on a form, based on two
fields one field [Start]and other field [End]both are
formatted for Short time. Made Text box with the Control
Source = ([End]-[Start])*24. Worked fine until I needed to
input work from second shift with a "Start" time of 23:30
and an "End" time of 3:00 this ended up giving a "Total"
time of -20.5 instead of the expected 3.5 hours.
Not real proficient at VBA yet, is there an easy way to
remedy this problem.

TIA

Access stores date/time values as a Double Float number, a count of
days and fractions of a day since midnight, December 30, 1899. Storing
just a pure time value gives you a time on that long-ago day.

I'd suggest storing the date and time both in the same field - 23:30
on February 17 is indeed 3.5 hoursearlier than 03:00 on February 18.

If you can't or don't want to do so, use a more complex expression:

Source = ([End] - [Start] + IIF([End] < [Start], 1, 0)) * 24.

or (better) use the DateDiff() function to calculate the time
difference in minutes.