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

[alexrubio]

Sounds like it's working. 1.25 hours - 4 hours = -3.75 hours.

Check out line 19. That's where Lunch is taken in consideration. You'll have to decide how you want to address that you are entering in four different shifts and then only one entry for Lunch. I put in a multiplier of four so that the one Field would apply to all four shifts. So, either three more Lunch TextBoxes need to be added, it's fine the way it is and maybe put a note on the Form letting the user know what is going on, or lastly, you could inspect each Shift and subtract a Lunch only if there is time entered against the shift.

Here is another version of the code where Lunch is only subtracted when a Shift has time against it.

Private Sub doPaint()

    Dim lTime As Long
    Dim lTemp As Long
    Dim lLunch As Long ' Long Lunch, Cracks me up
    
    ' Calculate Time in Minutes
    lTime = 0
    lLunch = (Nz([Lunch], 0) * 60)
    
    ' First Shift
    lTemp = (Nz(DateDiff("n", Me![Shift1Start], Me![Shift1End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
    
    ' Second Shift
    lTemp = (Nz(DateDiff("n", Me![Shift2Start], Me![Shift2End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
    
    ' Third Shift
    lTemp = (Nz(DateDiff("n", Me![Shift3Start], Me![Shift3End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
    
    ' Fourth Shift
    lTemp = (Nz(DateDiff("n", Me![Shift4Start], Me![Shift4End]), 0))
    If lTemp <> 0 Then lTime = lTime + lTemp - lLunch
        
    ' Format and Display
    Me.txtTotalHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

End Sub

One last thing, I used Round() instead of Fix() in the formatting portion. That was a mistake. Fix() is equivalent to truncating the value, which is what should be used in this case.

Long Luch, haha, just saw that, hilarious...!

Ok, I changed the Round to Fix... Now not sure what the Lunch field's formatting should be set to. I have it set so that it allows only to add time in "00:00" format, so for 30 minutes I enter "00:30", but it only takes off 5 minutes... So if the start is 01:00 AM and end is 02:00 AM, the totalhours field shows "00:55"...

[jforbes]

The five minutes for Lunch makes sense. It's not right but makes sense. I was expecting the user to enter how many hours for Lunch into the Lunch Textbox, like .5 for a half hour. But if the Formatting for Short Time is used, a Half hour would have a value of 0.0208 since a 24:00 has a value of 1. So .0208 * 60 minutes, times 4 shifts would give 5 minutes instead of 120 minutes.

The formulas would need to be modified to address the value of Lunch that is not in hours but in a fraction of a day. We need a multiplier of 1440, from 60 minutes times 24 hours = 1.

I don't know what your code looks like at this point so if you want to post it, we can get it going. Or if your code looks like Post #10, then use something like this for line 19:

    lTime = lTime - ((Nz([Lunch], 0) * 1440) * 4) ' Times four for four Employees... maybe have four separate instances of Lunch?

but hopefully, you are using code similar to post #14 as it addresses the lunch only if a shift is filled in. If so, then use something like the following for line 9:

    lLunch = (Nz([Lunch], 0) * 1440)

Tag, your it.

[alexrubio]

Ok, yeah using the code from post #10... I did not go the one on post #14 cause I thought you meant it should be used if we are giving lunch breaks at each shift, and that is not correct, just need it to deduct whatever time input on lunch field from the totalhours field...

I'll try what you just suggested and let you know what happens. Let me know if you think I should implement code on post #14 anyway regardless...

Thanks!

[alexrubio]

Ok, this is the code I'm using:

Private Sub doPaint()

    Dim lTime As Long

    ' Calculate Time in Minutes
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![PPW1MondayRegularStart], Me![PPW1MondayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![PPW1MondayFlex1Start], Me![PPW1MondayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![PPW1MondayCoreStart], Me![PPW1MondayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![PPW1MondayFlex2Start], Me![PPW1MondayFlex2End]), 0))
    lTime = lTime - ((Nz([PPW1MondayLunch], 0) * 1440) * 4) ' Times four for four Employees... maybe have four seperate instances of Lunch?

    ' Format and Display
    Me.txtPPW1MondayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

End Sub

I set the input mask of the lunch field to "00:00;0;_" as per the default, then I set it to short time under format... With a time from 01:00 AM to 02:00 AM with a "00:30" lunch the PPW1MondayHours field displays "-1:00", if I enter "01:00" in lunch total displays "-3:00"

[alexrubio]

Ok, yeah using the code from post #10... I did not go the one on post #14 cause I thought you meant it should be used if we are giving lunch breaks at each shift, and that is not correct, just need it to deduct whatever time input on lunch field from the totalhours field...

I'll try what you just suggested and let you know what happens. Let me know if you think I should implement code on post #14 anyway regardless...

Thanks!

I THINK I got it working now... I changed the:

    lTime = lTime - ((Nz([PPW1MondayLunch], 0) * 1440) * 4)

For this:

    lTime = lTime - ((Nz([PPW1MondayLunch], 0) * 1440) * 1)

The 1440*4 to 1440*1 and it seems to calculate it fine, the only thing that is bothering me is that it drops the leaving zero on lunch time so instead of looking like "00:30" it looks like "0:30", just looks weird... I think if we fix that, we might be good...

Then comes my pain staking process of getting the rest of the fields to work, Tuesday, Wednesday for PPW1 and then Monday-Friday for PPW2, but it should work just fine having worked out these kinks...

Thanks again jforbes, for your patience and GREAT help on this!

[jforbes]

That's good news. I'm not sure if there is anything that can be done about the formatting of Lunch. I looked into it a little, but I didn't see a formatting solution that would work. Let us know if you find one.

Typically, I would have the Lunch Field as a ComboBox with a list of values in minutes 10, 15, 20, 25... up to 2 hours for the user to select from. You could use this approach and create a ComboBox with two columns, the first a hidden key column with the available values and the second the formatted values like 00:10, 00:15, 00:20... up to 2:00. It's an option at least.

[alexrubio]

That's good news. I'm not sure if there is anything that can be done about the formatting of Lunch. I looked into it a little, but I didn't see a formatting solution that would work. Let us know if you find one.

Typically, I would have the Lunch Field as a ComboBox with a list of values in minutes 10, 15, 20, 25... up to 2 hours for the user to select from. You could use this approach and create a ComboBox with two columns, the first a hidden key column with the available values and the second the formatted values like 00:10, 00:15, 00:20... up to 2:00. It's an option at least.

Thanks jforbes,

That is exacatly like I have the other fields set, with a combobox so they can choose the times... Do you think by doing it this way it will display it correctly?, Because it does allow you to enter the leading zero, it just drops it as soon as you leave the field though... The interesting thing is, it does not happen in the totalhours field... if its 3 hours it shows "03:00", so I figured there has to be a way to do it for the lunch field, I'll try the combo if not will look around a bit...

No to implement the rest of the days of PPW1, yesterday I tried to do it all together in the same doPaint module, but it disabled the Monday fields... I tried to create another Dim of tTime (as not to conflict with lTime), but it did not work... Do you think all of it should be together, or should I create a separate doPaint module for each day? Once complete with PPW1 (Pay Period Week 1) I have to do the same for PPW2 Monday - Friday...

THANKS!

[jforbes]

I think the ComboBox will give you greater control over what is displayed. Also, I think it will be easier to use for most of your users.

You should be able to do everything in one procedure and you should be able to reuse your variables. Calculate, Format and Display Monday, then reset lTime to Zero then Calculate, Format and Display Tuesday, then reset lTime to Zero and so on.

The only thing that concerns me now is:

Once complete with PPW1 (Pay Period Week 1) I have to do the same for PPW2 Monday - Friday...

I'm not sure how you are structuring your tables, but I would expect it to be broken down enough that you wouldn't have separate elements for your weeks and what has been done so far would work for all pay periods and not just for the first one. You might want to check out http://bytes.com/topic/access/insigh...ble-structures Just to make sure you aren't getting yourself into a mess.

[alexrubio]

I think the ComboBox will give you greater control over what is displayed. Also, I think it will be easier to use for most of your users.

You should be able to do everything in one procedure and you should be able to reuse your variables. Calculate, Format and Display Monday, then reset lTime to Zero then Calculate, Format and Display Tuesday, then reset lTime to Zero and so on.

The only thing that concerns me now is:
I'm not sure how you are structuring your tables, but I would expect it to be broken down enough that you wouldn't have separate elements for your weeks and what has been done so far would work for all pay periods and not just for the first one. You might want to check out http://bytes.com/topic/access/insigh...ble-structures Just to make sure you aren't getting yourself into a mess.

So in other words you mean, do something like lTime = 0 then do everything for Monday, then another lTime = 0 then Tuesday, etc...?

On the table I have all fields in the format:

PPW1MondayRegul arStart
PPW1MondayRegul arEnd
PPW1MondayFlex1 Start
PPW1MondayFlex1 End
PPW1MondayCoreS tart
PPW1MondayCoreE nd
PPW1MondayFlex2 Start
PPW1MondayFlex2 End
PPW1MondayLunch
PPW1MondayHours (UNBOUND)

All the way to Friday, then the same exact thing but with "PPW2" for the second week... The form looks like an excel spreadsheet with the 10 columns and 10 rows...

[jforbes]

You got it, reset lTime in between each day.

If you only have the two weeks at a time I think you will be OK. My concern was that you were going to have an endless amount of weeks that would need to be developed for.

[alexrubio]

Let ya know when I complete PPW2 and if I come across any issues...

Thanks again so much for your continued help...!!!

[alexrubio]

jforbes,

All is working like a champ, you have been wonderful in helping me with this and I'm extremely greatful, thanks a mill...

I have stumbled across another issue, I will create a new question for it as it has nothing to do with this one...

Thanks Again!!!

[alexrubio]

Hi jforbes,

Hope all is well with ya... I was hoping you could help me to add a calculation to the code you helped me work on a few weeks ago...

Here is a what it looks like:

Private Sub doPaint()

    Dim lTime As Long
   
    ' Calculate Time in Minutes - PPW1Monday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1MondayRegularStart], Me![cboPPW1MondayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1MondayFlex1Start], Me![cboPPW1MondayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1MondayCoreStart], Me![cboPPW1MondayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1MondayFlex2Start], Me![cboPPW1MondayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1MondayLunch], 0) * 1440) * 1)
   
    ' Format and Display - PPW1Monday
    Me.txtPPW1MondayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
   
    ' Calculate Time in Minutes - PPW1Tuesday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1TuesdayRegularStart], Me![cboPPW1TuesdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1TuesdayFlex1Start], Me![cboPPW1TuesdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1TuesdayCoreStart], Me![cboPPW1TuesdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1TuesdayFlex2Start], Me![cboPPW1TuesdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1TuesdayLunch], 0) * 1440) * 1)
   
    ' Format and Display - PPW1Tuesday
    Me.txtPPW1TuesdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
   
    ' Calculate Time in Minutes - PPW1Wednesday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1WednesdayRegularStart], Me![cboPPW1WednesdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1WednesdayFlex1Start], Me![cboPPW1WednesdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1WednesdayCoreStart], Me![cboPPW1WednesdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1WednesdayFlex2Start], Me![cboPPW1WednesdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1WednesdayLunch], 0) * 1440) * 1)
   
    ' Format and Display - PPW1Wednesday
    Me.txtPPW1WednesdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
   
    ' Calculate Time in Minutes - PPW1Thursday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1ThursdayRegularStart], Me![cboPPW1ThursdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1ThursdayFlex1Start], Me![cboPPW1ThursdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1ThursdayCoreStart], Me![cboPPW1ThursdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1ThursdayFlex2Start], Me![cboPPW1ThursdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1ThursdayLunch], 0) * 1440) * 1)
   
    ' Format and Display - PPW1Thursday
    Me.txtPPW1ThursdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
   
    ' Calculate Time in Minutes - PPW1Friday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1FridayRegularStart], Me![cboPPW1FridayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1FridayFlex1Start], Me![cboPPW1FridayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1FridayCoreStart], Me![cboPPW1FridayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1FridayFlex2Start], Me![cboPPW1FridayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1FridayLunch], 0) * 1440) * 1)
   
    ' Format and Display - PPW1Friday
    Me.txtPPW1FridayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
   
    ' Calculate Time in Minutes - PPW2Monday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2MondayRegularStart], Me![cboPPW2MondayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2MondayFlex1Start], Me![cboPPW2MondayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2MondayCoreStart], Me![cboPPW2MondayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2MondayFlex2Start], Me![cboPPW2MondayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2MondayLunch], 0) * 1440) * 1)
   
    ' Format and Display - PPW2Monday
    Me.txtPPW2MondayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
  
    ' Calculate Time in Minutes - PPW2Tuesday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2TuesdayRegularStart], Me![cboPPW2TuesdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2TuesdayFlex1Start], Me![cboPPW2TuesdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2TuesdayCoreStart], Me![cboPPW2TuesdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2TuesdayFlex2Start], Me![cboPPW2TuesdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2TuesdayLunch], 0) * 1440) * 1)
   
    ' Format and Display - PPW2Tuesday
    Me.txtPPW2TuesdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
   
    ' Calculate Time in Minutes - PPW2Wednesday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2WednesdayRegularStart], Me![cboPPW2WednesdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2WednesdayFlex1Start], Me![cboPPW2WednesdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2WednesdayCoreStart], Me![cboPPW2WednesdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2WednesdayFlex2Start], Me![cboPPW2WednesdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2WednesdayLunch], 0) * 1440) * 1)
   
    ' Format and Display - PPW2Wednesday
    Me.txtPPW2WednesdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
   
    ' Calculate Time in Minutes - PPW2Thursday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2ThursdayRegularStart], Me![cboPPW2ThursdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2ThursdayFlex1Start], Me![cboPPW2ThursdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2ThursdayCoreStart], Me![cboPPW2ThursdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2ThursdayFlex2Start], Me![cboPPW2ThursdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2ThursdayLunch], 0) * 1440) * 1)
   
    ' Format and Display - PPW2Thursday
    Me.txtPPW2ThursdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
  
    ' Calculate Time in Minutes - PPW2Friday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2FridayRegularStart], Me![cboPPW2FridayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2FridayFlex1Start], Me![cboPPW2FridayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2FridayCoreStart], Me![cboPPW2FridayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2FridayFlex2Start], Me![cboPPW2FridayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2FridayLunch], 0) * 1440) * 1)

    ' Format and Display - PPW2Friday
    Me.txtPPW2FridayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

End Sub

Here is what I have to figure out, I have an unbound text field called txtTotalHours in which I need to display the total hours from PPW1Monday through PPW2Friday. I have tried to add this way:

Me.txtTotalHours = Me.txtPPW1MondayHours + Me.txtPPW1TuesdayHours + etc

But all it does is show the two hours side by side, like "04:1504:15 "... Then all the daily total fields are wiped clean, in other words it screws up the above code... I have to undo to get it back to working status...

Thanks again for your kind help as always...

Alex

[jforbes]

Hey Alex,

I think the most straight forward way would be to create a variable to store off a running total into.

The easiest way to explain it would be in code:

Private Sub doPaint()

    Dim lTime As Long
    [icode]Dim lTotalHours As Long[/icode]

    ' Calculate Time in Minutes - PPW1Monday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1MondayRegularStart], Me![cboPPW1MondayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1MondayFlex1Start], Me![cboPPW1MondayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1MondayCoreStart], Me![cboPPW1MondayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1MondayFlex2Start], Me![cboPPW1MondayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1MondayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW1Monday
    Me.txtPPW1MondayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

    ' Calculate Time in Minutes - PPW1Tuesday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1TuesdayRegularStart], Me![cboPPW1TuesdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1TuesdayFlex1Start], Me![cboPPW1TuesdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1TuesdayCoreStart], Me![cboPPW1TuesdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1TuesdayFlex2Start], Me![cboPPW1TuesdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1TuesdayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW1Tuesday
    Me.txtPPW1TuesdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

    ' Calculate Time in Minutes - PPW1Wednesday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1WednesdayRegularStart], Me![cboPPW1WednesdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1WednesdayFlex1Start], Me![cboPPW1WednesdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1WednesdayCoreStart], Me![cboPPW1WednesdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1WednesdayFlex2Start], Me![cboPPW1WednesdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1WednesdayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW1Wednesday
    Me.txtPPW1WednesdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

    ' Calculate Time in Minutes - PPW1Thursday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1ThursdayRegularStart], Me![cboPPW1ThursdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1ThursdayFlex1Start], Me![cboPPW1ThursdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1ThursdayCoreStart], Me![cboPPW1ThursdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1ThursdayFlex2Start], Me![cboPPW1ThursdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1ThursdayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW1Thursday
    Me.txtPPW1ThursdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

    ' Calculate Time in Minutes - PPW1Friday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1FridayRegularStart], Me![cboPPW1FridayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1FridayFlex1Start], Me![cboPPW1FridayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1FridayCoreStart], Me![cboPPW1FridayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW1FridayFlex2Start], Me![cboPPW1FridayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW1FridayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW1Friday
    Me.txtPPW1FridayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

    ' Calculate Time in Minutes - PPW2Monday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2MondayRegularStart], Me![cboPPW2MondayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2MondayFlex1Start], Me![cboPPW2MondayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2MondayCoreStart], Me![cboPPW2MondayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2MondayFlex2Start], Me![cboPPW2MondayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2MondayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW2Monday
    Me.txtPPW2MondayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

    ' Calculate Time in Minutes - PPW2Tuesday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2TuesdayRegularStart], Me![cboPPW2TuesdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2TuesdayFlex1Start], Me![cboPPW2TuesdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2TuesdayCoreStart], Me![cboPPW2TuesdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2TuesdayFlex2Start], Me![cboPPW2TuesdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2TuesdayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW2Tuesday
    Me.txtPPW2TuesdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

    ' Calculate Time in Minutes - PPW2Wednesday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2WednesdayRegularStart], Me![cboPPW2WednesdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2WednesdayFlex1Start], Me![cboPPW2WednesdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2WednesdayCoreStart], Me![cboPPW2WednesdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2WednesdayFlex2Start], Me![cboPPW2WednesdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2WednesdayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW2Wednesday
    Me.txtPPW2WednesdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

    ' Calculate Time in Minutes - PPW2Thursday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2ThursdayRegularStart], Me![cboPPW2ThursdayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2ThursdayFlex1Start], Me![cboPPW2ThursdayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2ThursdayCoreStart], Me![cboPPW2ThursdayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2ThursdayFlex2Start], Me![cboPPW2ThursdayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2ThursdayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW2Thursday
    Me.txtPPW2ThursdayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)

    ' Calculate Time in Minutes - PPW2Friday
    lTime = 0
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2FridayRegularStart], Me![cboPPW2FridayRegularEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2FridayFlex1Start], Me![cboPPW2FridayFlex1End]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2FridayCoreStart], Me![cboPPW2FridayCoreEnd]), 0))
    lTime = lTime + (Nz(DateDiff("n", Me![cboPPW2FridayFlex2Start], Me![cboPPW2FridayFlex2End]), 0))
    lTime = lTime - ((Nz([cboPPW2FridayLunch], 0) * 1440) * 1)
    [icode]lTotalHours = lTotalHours + lTime[/icode]

    ' Format and Display - PPW2Friday
    Me.txtPPW2FridayHours.Value = Right("00" & Fix(lTime / 60), 2) & ":" & Right("00" & lTime Mod 60, 2)
    
[icode]    ' Format and Display - Total Hours
    Me.txtTotalHours = Right("00" & Fix(lTotalHours / 60), 2) & ":" & Right("00" & lTotalHours Mod 60, 2)[/icode]
 
End Sub

[alexrubio]

Hi jforbes,

Thanks much..., It seems to be partially working, when I enter info for one record and move on to the next, it carries the info into the other record. For example, I entered the following times on PPW1Monday Column of the first record:

Regular Start:
Regular End:
Flex1 Start: 09:30 AM
Flex 1 End: 10:00 AM
Core Start: 10:00 AM
Core End: 3:00 PM
Flex 2 Start: 3:00 PM
Flex 2 End: 7:00 PM
Lunch: 0:30
Total: 09:00 (txtPPW1MondayH ours)

txtTotalHours field Shows 09:00 (so far so good)

When I move to the next record the txtPPW1MondayHo urs field shows "09:00" instead of "00:00" and the same with txtTotalHours field... If I fill in more days prior to moving to new record more time gets added to txtTotalHours and shown on the txtPPwXXXXXHour s fields...

Remember that the txtPPwXXXXXHour s fileds are unbound, all others are bound...

THANKS AGAIN!