help with code for complexed calculation involving time

[Astron2012]

Hi I have a database to show the employees schedule. I have a form 'JobDetails' with subform 'JobDetailsSub'.

The mainform has a textbox with a date that is used to bring up the jobs in the subform.

The subform has 5 fields;

Start Date
Start Time
End Date
End Time
HoursToday (unbound textbox, time format)

I want to write code for the subform on open that will perform a complexed calculation in the HoursToday field. This is because I have some jobs that may spread over a few days but I only want to see the hours spent on that job by day.

I have written the following but it is completely wrong as I am useless with code, could anyone point out where i'm going wrong with the code?
Private Sub Form_Open(Cancel As Integer)
If JobDetails.[TextDate] = [StartDate] Then
Me.HoursToday = [EndTime] - [StartTime]
Else
If JobDetails.TextDate > [StartDate] and JobDetails.TextDate < [EndDate] Then


Me.HoursToday = 08:00:00
Else
If JobDetails.TextDate > [StartDate] and JobDetails.TextDate = [EndDate] Then
Me.HoursToday = [EndTime] - 09:00:00
End Sub

If someone could help me with this I would be extremely grateful!

Thanks

[June7]

Math operations done directly with dates defaults to day units. If you want the calc in other units must use DateDiff function.

Refer to the TextDate box on the main form with:
Forms!JobDetails.TextDate
or
Form_JobDetails.TextDate

[ssanfu]

The times need delimiters - ie #8:00#

You have two "IF" statements without closing "End If" statements
Should be:

<snip>
        Me.HoursToday = [EndTime] - #9:00:00 AM#
      End If
    End If
   End Sub

Not sure what you are trying to do.
I think you are using the wrong event to do the calculations.......

[Astron2012]

Hi Steve,

I think you're right. I managed to get the first IF statement to work but then because the subform is set to continuous forms I wanted the value in the HoursToday field to match each record, but at the moment it only works for the first record.

I should probably us the expression builder in the field itself but I don't have a clue how to set this up.

I have 3 conditions that I need the field to look at;

1) If Forms!JobDetails.TextDate matches the date in StartDate then I want the field to calculate EndTime minus StartTime....... or

2) If Forms!JobDetails.TextDate is after the date in StartDate AND before the date in EndDate then the value of the field should be 08.00....... or

3) If Forms!JobDetails.TextDate is after the date in StartDate BUT equals the date in EndDate then the value should be the time difference between 09.00.00 and EndTime

I really hope someone can help me with this, I have spent many hopeless hours trying to figure this out!

Or if anyone has any better suggestions on how to perform this action I would be grateful.

Thanks

[ssanfu]

(warning - this is untested code) You can try this code.... not sure what types your fields are

And I don't think you want to use the form open event. But here it is:

Private Sub Form_Open(Cancel As Integer)
   Dim txtDate As Date  'mainform date control

   Dim vStartDate As Date   'subform date control
   Dim vEndDate As Date   'subform date control

   Dim vStartTime As Date   'subform time control
   Dim vEndTime As Date   'subform time control

'mainform
   txtDate = Me.[TextDate]

   'from subform >>  Me!Subform1.Form!ControlName
   vStartDate = Me.JobDetailsSub.Form!StartDate
   vEndDate = Me.JobDetailsSub.Form!EndDate
   vStartTime = Me.JobDetailsSub.Form!Starttime
   vEndTime = Me.JobDetailsSub.Form!EndTime


   '1) If Forms!JobDetails.TextDate matches the date in StartDate then
   '     I want the field to calculate EndTime minus StartTime
   If txtDate = vStartDate Then
      Me.HoursToday = DateDiff("h", vStartTime, vEndTime)
   Else
      '2) If Forms!JobDetails.TextDate is after the date in StartDate AND before the date in EndDate then
      '     the value of the field should be 08.00....... or
      If txtDate > vStartDate And txtDate < vEndDate Then
         Me.HoursToday = 8
      Else
         '3) If Forms!JobDetails.TextDate is after the date in StartDate BUT equals the date in EndDate then
         '    the value should be the time difference between 09.00.00 and EndTime
         If txtDate > vStartDate And txtDate = vEndDate Then
            Me.HoursToday = DateDiff("h", #9:00:00 AM#, vEndTime)
         End If
      End If
   End If
End Sub

"Me.HoursToday" might have to be "Me.JobDetailsSub.Form!HoursToday"

[Astron2012]

Thank you Steve, I will give this a try now. I wasn't sure what event to use and I don't think the form open even is best either, as I will need the box to requery if the user selects a different date in the main form. So would need to happen when the subform requeries, do you know what event that would be?

Thanks

[Astron2012]

I have tried the code but not quite working as yet. I think it may be to do with the format of the HoursToday textbox. When I open the forms it just shows 00:00 instead of the actual result. I have formatted it to short time but when I click into it it shows the time and the date. The date is 31/12/1899 which perhaps means it could be working but just not displaying the result in the correct format? Do I need to change the format in the code itself maybe?

I had to change the code a little anyway to get the form to open. Here is the updated code

Private Sub Form_Open(Cancel As Integer)
Dim txtDate As Date 'mainform date control
Dim vStartDate As Date 'subform date control
Dim vEndDate As Date 'subform date control
Dim vStartTime As Date 'subform time control
Dim vEndTime As Date 'subform time control
'mainform
'from subform >> Me!Subform1.Form!ControlName
vStartDate = Me.StartDate
vEndDate = Me.EndDate
vStartTime = Me.StartTime
vEndTime = Me.EndTime

'1) If Forms!JobDetails.TextDate matches the date in StartDate then
' I want the field to calculate EndTime minus StartTime
If Forms!JobDetails.TextDate = vStartDate Then
Me.HoursToday = DateDiff("h", vStartTime, vEndTime)
Else
'2) If Forms!JobDetails.TextDate is after the date in StartDate AND before the date in EndDate then
' the value of the field should be 08.00....... or
If Forms!JobDetails.TextDate > vStartDate And txtDate < vEndDate Then
Me.HoursToday = 8
Else
'3) If Forms!JobDetails.TextDate is after the date in StartDate BUT equals the date in EndDate then
' the value should be the time difference between 09.00.00 and EndTime
If Forms!JobDetails.TextDate > vStartDate And Forms!JobDetails.TextDate = vEndDate Then
Me.HoursToday = DateDiff("h", #9:00:00 AM#, vEndTime)
End If
End If
End If
End Sub

If you could help on this that would be great

Thank you so much

[Astron2012]

OK i realised I needed to Dim the time as integer. I have done this and now only have the time value but it is only showing as 00.00.00 instead of 08.00.00 for example. So not working
Not sure what to try next?

[ssanfu]

OK, I missed that you had the code in the subform module. So the code changes would be (change my code):

'mainform
   txtDate = Me.[TextDate]
   'from subform >>  Me!Subform1.Form!ControlName
   vStartDate = Me.StartDate
   vEndDate = Me.EndDate
   vStartTime = Me.StartTime
   vEndTime = Me.EndTime

Also note that when you subtract two times, you don't get a time, you get the time interval. So if you subtract 8AM from 8PM, you get 12 hours, not 12:00.
In the table (if it is in a table),"HoursToday" type should be Single or Text (Text if there is not going to used to do math). It should not be formatted as time.

If you want the hours and minutes, the Datediff() function interval should be "n" (minutes). Then do the calc to convert it hours (divide by 60).

[Astron2012]

Thanks so much for your help,

That has made a figure show now, the only problem is the figures are wrong, I will look at the calculations again to see why this is. But also the main problem is that all the records are showing the same figure (the form is set to continuous forms) and i need the figure to change for each one, the idea is that users can see what jobs are to be worked today and for how long and they can see how many hours are available to book. Because some jobs spread over more than one day, this has made the calculation complicated because i need to only show the hours that job is using for the day selected.

Is it better to put the calculation as an expression in the HoursToday textbox itself in order to get a different result for each record, or is this still possible with code?

Nightmare lol

[ssanfu]

That has made a figure show now, the only problem is the figures are wrong,

Doesn't tell me much. As far as I can tell, the calculations are what you provided. Remember, the results are NOT a time (hh:mm) - it is the number of hours (X.X hrs) between the times. Do not format them as time.

I will look at the calculations again to see why this is. But also the main problem is that all the records are showing the same figure (the form is set to continuous forms) and i need the figure to change for each one, the idea is that users can see what jobs are to be worked today and for how long and they can see how many hours are available to book. Because some jobs spread over more than one day, this has made the calculation complicated because i need to only show the hours that job is using for the day selected.

Is it better to put the calculation as an expression in the HoursToday textbox itself in order to get a different result for each record, or is this still possible with code?

Convert it to a function and enter the function in the query.

The function: (WARNING - this is air code!!)

Function CalcHrs(txtDate As Date, pSDate As Date, pEDate As Date, pSTime As Date, pETime As Date) As Single
'returns hours.... 9 or 8.45

   If txtDate = pSDate Then
      CalcHrs = (DateDiff("n", pSTime, pETime)) / 60
   ElseIf txtDate > pSDate And txtDate < pEDate Then
      CalcHrs = 8
   ElseIf txtDate > pSDate And txtDate = pEDate Then
      CalcHrs = (DateDiff("n", #9:00:00 AM#, pETime)) / 60
   Else
      CalcHrs = -1111  '<=this is to indicate an error
   End If

   '1) If Forms!JobDetails.TextDate matches the date in StartDate then
   '      I want the field to calculate EndTime minus StartTime
   '2) If Forms!JobDetails.TextDate is after the date in StartDate AND before the date in EndDate then
   '      the value of the field should be 08.00....... or
   '3) If Forms!JobDetails.TextDate is after the date in StartDate BUT equals the date in EndDate then
   '      the value should be the time difference between 09.00.00 and EndTime

End Function

To call it:
In a column in the query for the subform "JobDetails":

Hrs: CalcHrs(Me.Parent!TextDate, StartDate, EndDate, StartTime, EndTime)

OR

In the control source for a text box:
=CalcHrs(Me.Parent!TextDate, StartDate, EndDate, StartTime, EndTime)

[Astron2012]

Hi,

Its Ok I finally managed to sort it, just decided to do 4 different text boxes, one for each case and then total with an expression in each one, working fine now

Thanks for all your help