Steve,
Thank you for taking the time to assist with this issue.
It is my fault in not going into the full background / details. In my
ignorance I was trying to provide a brief summary of what I thought was the
main issue, in hindsight I see my error.
You are correct in that I do have a Start and End Date in a table and as
you suggest the difference is a calculated field. BUT the calculated
difference is in working days with the definition of a working day being
Mon - Fri and from 9:00am to 5:00pm. This calculation is in a module, but it
produces its result in a string ( I have attached a copy of the module
below.) Which brings us full circle to my first post
Function WorkingHours(ByVal SDate As Date, ByVal EDate As Date) As String
Const SDay As Integer = 8 '8am start
Const EDay As Integer = 18 '6pm finish
Dim lngDays As Long
Dim lngHours As Long
Dim lngMins As Single
Dim lngSecs As Single
Dim lngCount As Long
WorkingHours = "0"
If DatePart("h", SDate) < SDay Then
'Start time before SDay
'Move the time to the start of the working day
SDate = CVDate(Format$(SDate, "dd mmm yyyy") & " " & Format$(SDay,
"00") & ":00:00")
End If
If DatePart("w", SDate, vbMonday) > 5 Then
'Start day not weekday
'Move it to the start hour of monday
Do
If DatePart("w", SDate, vbMonday) = 1 Then Exit Do
SDate = DateAdd("d", 1, SDate)
Loop
SDate = CVDate(Format$(SDate, "dd mmm yyyy") & " " & Format$(SDay,
"00") & ":00:00")
End If
If SDate > EDate Then
Exit Function
End If
If DatePart("Y", SDate) = DatePart("Y", EDate) Then
'Same day
If DatePart("h", EDate) < EDay Then
'Straight subtraction
WorkingHours = "0:" & Format$(EDate - SDate, "hh:mm:ss")
Exit Function
Else
EDate = CVDate(Format$(SDate, "dd mmm yyyy") & " " & CStr(EDay) &
":00:00")
WorkingHours = Format$(EDate - SDate, "hh:mm:ss")
Exit Function
End If
End If
If DatePart("w", EDate, vbMonday) > 5 Then
'Ends on a weekend
lngHours = 0 'The number of hours on the last day
lngMins = 0 'The number of minutes on the last day
lngSecs = 0 'The number of minutes on the last day
Else
'Ends on a weekday
If DatePart("h", EDate) < SDay Then
'Finished before start time
lngHours = 0 'The number of hours on the last Day
lngMins = 0 'The number of minutes on the last Day
lngSecs = 0 'The number of minutes on the last Day
Else
'Finished after start time
lngHours = DatePart("h", EDate) - SDay 'The number of hours on
the last day
lngMins = DatePart("n", EDate) 'The number of minutes on the
last day
lngSecs = DatePart("s", EDate) 'The number of minutes on the
last day
End If
End If
Do
If Int(SDate) > Int(EDate) Then
'Ooops
WorkingHours = "0"
Exit Do
End If
'Step back to start day, stepping over weekends
EDate = DateAdd("d", -1, EDate)
If DatePart("w", EDate, vbMonday) < 6 Then
'This is a weekday
If Int(SDate) = Int(EDate) Then
'We are back to the start date
'Add it to the time from the start day
EDate = CVDate(Format$(EDate, "dd mmm yyyy") & " " &
CStr(EDay) & ":00:00")
lngHours = lngHours + DatePart("h", (EDate - SDate))
lngMins = lngMins + DatePart("n", (EDate - SDate))
lngSecs = lngSecs + DatePart("s", (EDate - SDate))
If lngSecs > 59 Then
lngSecs = lngSecs - 60
lngMins = lngMins + 1
End If
If lngMins > 59 Then
lngMins = lngMins - 60
lngHours = lngHours + 1
End If
WorkingHours = CStr(Int(lngHours \ 8) & ":" &
Format$(lngHours Mod 8, "00") & ":" & Format$(lngMins, "00") & ":" &
Format$(lngSecs, "00"))
Exit Do
Else
If Int(SDate) > Int(EDate) Then
WorkingHours = "0"
Exit Do
Else
'Add in a full day
lngHours = lngHours + EDay - SDay
End If
End If
End If
Loop
End Function
PC Datasheet said:
Bob,
Since one form of the data includes days, I presume the data represents the
difference between two Date/Time fields. This is where you need to fix the
problem. First of all, if you are recording the difference in a table, that is
incorrect. You should only be recording the start and end. Then you should
calculate the difference where you need it in a query.
To calculate the difference and be able to work on it, you need to use the
DateDiff function to get the difference in seconds.. It looks like this:
DateDiff("s",Start,End)
So in your query you put the following expression in a field of your query:
MyDifference
ateDiff("s",Start,End)
You can now get the min , max, avg etc, by converting your query to a totals
query by clicking on the Sigma button in the toolbar at the top of the screen
and then changing Group By in the MyDifference field to min , max, avg or
whatever. Finally, you can get the final value in the format you want by using
the Format function.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Box 666 said:
Steve,
You are correct in that the string represents either d:hh:mm:ss or
hh:mm:ss, but because it is in a string I have been unable to work on it
(min , max, avg etc.) The only way I could find to work on it was to convert
all fields to seconds and then manipulate the data.
My issue is that I am unable to format the string as date/time which was
why I was trying to find out how to extract the individual elements of the
string.
Bob
Steve said:
Bob,
You can simlify this by making your field a Date/Time field. Then you
can
use:
MyHour = Hour(Me!NameOfDateTimeField)
MyMinute = Minute(Me!NameOfDateTimeField)
MySecond = Second(Me!NameOfDateTimeField)
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
I have a string that I need to split out into its original
components.
The
string will appear in one of 2 formats either
0:11:22:33 or as 11:22:33
I now need to seperate out each segment, I can get the "33" out ok using
Right([seg],2)
I believe I need to use a combination of Mid and InStr to extract
the
rest,
but because the other items in the string are not always consistant
(sometimes with a "0:" sometimes with out) I am struggling to
achieve
this.
Can anybody help please
Thanks
Bob
