Time duration 2

[BrianLe]

I have a tblActivity with fields StartDate and EndDate with Date Type as Date/Time (no specified format), and a report with tbxDuration with control source

=[EndDate]-[Start][Date]

I would like to change the control source for tbxDuration so that the period of time from 9:00 AM to 9:15 AM, 11:45 AM to 12:30 PM, and 2:00 PM to 2:15 PM are not included in the duration calculation.

Example 1. If the start date is 4/6/08 8:00 AM and the end date is 4/6/08 11:30 AM, the duration would be 3.25 hours.

Example 2. If the start date is 4/6/08 8:00 AM and the end date is 4/6/08 2:00 PM, the duration would be 5.00 hours.

Example 3. If the start date is 4/6/08 9:15 AM and the end date is 4/6/08 at 4:00 PM, the duration would be 5.75 hours.

Thanks,

Brian

 

[dhookom]

You are referencing a number of time periods that might will change over time so any solution that hard-codes these values into a complex expression should be avoided.

I would create a small user-defined function.

Public ActivityDuration(datStart as Date, datEnd as Date) as double
'remove  9:00 AM to 9:15 AM, 11:45 AM to 12:30 PM, and 2:00 PM to 2:15 PM
    Dim dblTotalTime as Double
    dblTotalTIme = datEnd - datStart
    If datStart < #9:15# AND datEnd > #9:15# Then
        If datStart < #9:00# Then
            dblTotalTime = dblTotalTime - (#9:15#-#9:00#)
         Else
            dblTotalTime = dblTotalTime - (datStart-#9:00#)
        End If
    End If
' add more code like this
    'convert to hours and return result
    ActivityDuration = dblTotalTime * 24 
End

Duane
Hook'D on Access
MS Access MVP

 

[BrianLe]

Thanks Duane. I forget how to use public functions. I'll try this tomorrow.

 

[BrianLe]

I need some more help on where to put the code. I tried putting it into the code for the form that opens the report

Public Sub ActivityDuration(StartDate As Date, EndDate As Date)
'remove  9:00 AM to 9:15 AM, 11:45 AM to 12:30 PM, and 2:00 PM to 2:15 PM
    Dim dblTotalTime As Double
    dblTotalTime = EndDate - StartDate
    If StartDate < #9:15:00 AM# And EndDate > #9:15:00 AM# Then
        If StartDate < #9:00:00 AM# Then
            dblTotalTime = dblTotalTime - (#9:15:00 AM# - #9:00:00 AM#)
         Else
            dblTotalTime = dblTotalTime - (StartDate - #9:00:00 AM#)
        End If
    End If
' add more code like this
    'convert to hours and return result
    ActivityDuration = dblTotalTime * 24
End Sub

and added "Call ActivityDuration" to the click event for the button that opens the report.

Private Sub Toggle10_Click()

Call ActivityDuration
   
DoCmd.OpenReport "Cars Unloaded", acViewPreview

End Sub

I get a compile error on "Call ActivityDuration"

 

[SkipVought]

ActivityDuration needs TWO date arguments.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[BrianLe]

Skip,

I tried

Private Sub Toggle10_Click()

Call ActivityDuration (tblActivity.StartDate, tblActivity.EndDate)
   
DoCmd.OpenReport "Cars Unloaded", acViewPreview

End Sub

But I get variable not defined error on "tblActivity".

I'm not sure we are on the right track.

There is a StartDate and EndDate for each record in tblActivity.

My report is based on the following query qryActivityDumping2.

SELECT tblActivity.ActivityID, tblActivity.StartDate, tblActivity.EndDate, tblActivity.CrewSize, tblActivity.TStamp, tblActivity.DBelt, tblActivity.TrainID, tblActivity.TrainPartID, tblActivity.B2Belt, tblActivity.HandlingOptionNo, tblHandlingOption.HandlingOption, tblActivity.ProcessID, tblActivity.CarsUnloaded, tblActivity.PileCarWeight, tblActivity.StackingTubeCarWeight, tblActivity.Notes, tblTrain.TrainYear, tblTrain.TrainNo, tblTrainOrder.NumberOfCars, tblTrainOrder.Weight
FROM (tblHandlingOption INNER JOIN (tblActivity INNER JOIN tblTrain ON tblActivity.TrainID = tblTrain.TrainID) ON tblHandlingOption.HandlingOptionNo = tblActivity.HandlingOptionNo) INNER JOIN tblTrainOrder ON tblTrain.TrainID = tblTrainOrder.TrainID
WHERE (((tblActivity.ProcessID)=2) AND ((DateValue([tblActivity].[StartDate])) Between [Forms]![frmActivityDumpingReport]![tbxStartDate] And ([Forms]![frmActivityDumpingReport]![tbxEndDate])));

What I want to show in the report are all the queried activities between the start date and end date selected on frmActivityDumpingReport, but for the tbxDuration on the report to show the time between tblActivity StartDate and tblActivity EndDate, minus the exluded times 9:00 to 9:15, etc. for each line in the report.

Thanks,

Brian

 

[SkipVought]

Neither tblActivity.StartDate nor tblActivity.EndDate are ONE SINGLE VALUE.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[dhookom]

The function (my bad on leaving that part out) that I was suggesting would replace your control source of:

=[EndDate]-[Start][Date]

so it would be:

=ActivityDuration([StartDate],[EndDate])

Duane
Hook'D on Access
MS Access MVP

 

[BrianLe]

I put into the tbxDuration control source

=ActivityDuration([StartDate],[EndDate])

and put into the report code

Public Sub ActivityDuration(StartDate As Date, EndDate As Date)
'remove  9:00 AM to 9:15 AM, 11:45 AM to 12:30 PM, and 2:00 PM to 2:15 PM
    Dim dblTotalTime As Double
    dblTotalTime = EndDate - StartDate
    If StartDate < #9:15:00 AM# And EndDate > #9:15:00 AM# Then
        If StartDate < #9:00:00 AM# Then
            dblTotalTime = dblTotalTime - (#9:15:00 AM# - #9:00:00 AM#)
         Else
            dblTotalTime = dblTotalTime - (StartDate - #9:00:00 AM#)
        End If
    End If
' add more code like this
    'convert to hours and return result
    ActivityDuration = dblTotalTime * 24
End Sub

When I run the report, I get #Name? for the value in tbxDuration.

 

[SkipVought]

Where is the code for tbxDuration?

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[dhookom]

The procedure is a function not a sub. This function should be located in a standard module. The module name must not be the name of the function.

Public Function ActivityDuration(StartDate As Date, EndDate As Date) as Double
'remove  9:00 AM to 9:15 AM, 11:45 AM to 12:30 PM, and 2:00 PM to 2:15 PM
    Dim dblTotalTime As Double
    dblTotalTime = EndDate - StartDate
    If StartDate < #9:15:00 AM# And EndDate > #9:15:00 AM# Then
        If StartDate < #9:00:00 AM# Then
            dblTotalTime = dblTotalTime - (#9:15:00 AM# - #9:00:00 AM#)
         Else
            dblTotalTime = dblTotalTime - (StartDate - #9:00:00 AM#)
        End If
    End If
' add more code like this
    'convert to hours and return result
    ActivityDuration = dblTotalTime * 24
End Function

Duane
Hook'D on Access
MS Access MVP

 

[BrianLe]

I'm getting just the value of EndDate-StartDate)*24 in tbxDuration. Since my StartDate and EndDate have both date and hour components (4/7/2008 9:00 AM), I think I need something other than #9:15:00 AM", etc. to specify the times in the IF Statement.

 

[SkipVought]

    If StartDate [b]- INT(StartDate)[/b] < #9:15:00 AM# And EndDate [b]- INT(EndDate)[/b] > #9:15:00 AM# Then

/

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[dhookom]

Your example data had only records on the same date. You will need to modify the code to handle the dates and times.

Duane
Hook'D on Access
MS Access MVP

 

[BrianLe]

That did the trick.

Here's the code I put in "Module1" that looks good so far.

Public Function ActivityDuration(StartDate As Date, EndDate As Date) As Double
'remove  9:00 AM to 9:15 AM, 11:45 AM to 12:30 PM, and 2:00 PM to 2:15 PM
    Dim dblTotalTime As Double
    dblTotalTime = EndDate - StartDate
    
    If (StartDate - Int(StartDate)) < #9:15:00 AM# And (EndDate - Int(EndDate)) > #9:15:00 AM# Then
        If (StartDate - Int(StartDate)) < #9:00:00 AM# Then
            dblTotalTime = dblTotalTime - (#9:15:00 AM# - #9:00:00 AM#)
         Else
            dblTotalTime = dblTotalTime - (StartDate - Int(StartDate) - #9:00:00 AM#)
        End If
    End If
    
    If (StartDate - Int(StartDate)) < #11:45:00 AM# And (EndDate - Int(EndDate)) > #12:30:00 PM# Then
        If (StartDate - Int(StartDate)) < #11:45:00 AM# Then
            dblTotalTime = dblTotalTime - (#12:30:00 PM# - #11:45:00 AM#)
         Else
            dblTotalTime = dblTotalTime - (StartDate - Int(StartDate) - #11:45:00 AM#)
        End If
    End If
        
    If (StartDate - Int(StartDate)) < #2:00:00 PM# And (EndDate - Int(EndDate)) > #2:15:00 PM# Then
        If (StartDate - Int(StartDate)) < #2:00:00 PM# Then
            dblTotalTime = dblTotalTime - (#2:15:00 PM# - #2:00:00 PM#)
         Else
            dblTotalTime = dblTotalTime - (StartDate - Int(StartDate) - #2:00:00 PM#)
        End If
    End If
    
     If (StartDate - Int(StartDate)) < #4:45:00 PM# And (EndDate - Int(EndDate)) > #5:30:00 PM# Then
        If (StartDate - Int(StartDate)) < #4:45:00 PM# Then
            dblTotalTime = dblTotalTime - (#5:30:00 PM# - #4:45:00 PM#)
         Else
            dblTotalTime = dblTotalTime - (StartDate - Int(StartDate) - #4:45:00 PM#)
        End If
    End If
    
' add more code like this
    'convert to hours and return result
    ActivityDuration = dblTotalTime * 24
End Function

Thanks,

Brian

 

[dhookom]

I think you have a good handle on it. You have to do a lot of testing on this type of calculation to make sure you account for all varieties of data.

Duane
Hook'D on Access
MS Access MVP