Calculate time on form with 4 shifts, lunch, and total on Access Form

[alexrubio]

Are you using Unbound Fields? Or are you Bound to fields with a Date/Time Datatype? Are you getting an Error? What is it that is not working for you?

This part confused me: I would think the total time would be a Number and not a Formated Date/Time.

I think what you have, formula wise, will work against bound Controls, at least if the day is the same for both the start and end.
You may want to consider using the DateDiff() function. DateDiff() works on Dates and can work on Strings:

?DateDiff("s", "8:00am", "3:30pm")/3600
>7.5

Hi jforbes, thanks for your reply,

Yes, the start, end, and Lunch fields are bound, but the totalhours field will not, as it will just soppose to calculate the total, and I need it to show in HH:MM hours and minutes, so if the total hours worked totals to three hours and twenty minutes it should show as 02:20 or 2:20...

I tried DateDiff, but was not successful, how would I pluck my fields into your suggestion above?

Thanks again,

Alex

[jforbes]

Are you using Unbound Fields? Or are you Bound to fields with a Date/Time Datatype? Are you getting an Error? What is it that is not working for you?

This part confused me:

My goal is to get the "TotalHours " field to display a total time of the shifts, minus the "Lunch" field. TotalTime needs to be displayed in HH:MM AM/PM format.

I would think the total time would be a Number and not a Formated Date/Time.

I think what you have, formula wise, will work against bound Controls, at least if the day is the same for both the start and end.
You may want to consider using the DateDiff() function. DateDiff() works on Dates and can work on Strings:

?DateDiff("s", "8:00am", "3:30pm")/3600
>7.5

[alexrubio]

I'm having a very hard time calculating time on an access form. I have the following fields:

Shift1Start
Shift1End

Shift2Start
Shift2End

Shift3Start
Shift3End

Shift4Start
Shift4End

Lunch

TotalHours

The times entered above are on textboxes using medium time format (HH:MM AM or HH:MM PM). They do not cross over to the next day, they are all within one work day.

My goal is to get the "TotalHours " field to display a total time of the shifts, minus the "Lunch" field. TotalTime needs to be displayed in HH:MM AM/PM format.

I have tried:

=([Shift1End]-[Shift1Start])*24

Just to test before proceeding to attempt including the rest of the shifts, but I get the TotalHours field to display the time in decimal value. I have tried all kinds of variations but cannot nail the correct sintax/combination. I'm placing the code in the Control Source of the TotalHours field...

If anyone can please give me a hand would this I would really appreciate it.

In advance, Thank You!