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

[alexrubio]

I think I finally found the culprit...! Don't understand why it would affect it that way, but for some reason it was...

The unbound field called txtTotalHours was once bound to TotalHours, before I inserted your code, I unbound it and changed its "Other" name to txtTotalHours, I had some other code to change font color and field lock status... I had forgotten to change that code to reflect the new name...

Here is that code:

Me.TotalHours.BackColor = vbRed
Me.TotalHours.ForeColor = vbRed
Me.TotalHours.Locked = True

When I changed the above to "txtTotalHo urs" it worked immediately...

SO once again, you are the main man and got it working as usual, thanks a mill!

Sorry for the confusion!!!

[alexrubio]

Ok, I'm using Single Form and this happens when I click the right arrow at the bottom of the record to go into a new record.

You are correct about the totals, I need a full total for the two weeks.

The code is the same as is on post #28, plus the changes you added on your post #29.

Let me know if you need additional info...

[jforbes]

That is interesting.

I'm not 100% sure of what happens when you move to the next record. Are you moving to a different physical record in the database? Also, is the Form's DefaultView Property set to Single Form or Continuous Forms? Continuous Forms and Unbound Fields don't get along very well.

Also, I assumed that the Total Hours that you want to display is for the whole two weeks. Is this correct, or is there supposed to be a Total for each week?

My guess is either lTotalHours isn't getting reset at the right time or it's getting set from the from the Form after it has been created.

Last thing you could do is to repost what code you are using.

[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!

[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,

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

[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]

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

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

[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]

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]

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]

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]

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]

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!

[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"