Difference between two times

[Jahanzaib]

Hi,
I have a table named 'Shifts', having 3 fields n with this data.

ShiftID TimeFrom TimeTo
1 11:00:00 AM 06:59:59 PM
2 07:00:00 PM 01:59:59 AM
3 02:00:00 AM 10:59:59 AM

And I have another table named "Complaints", having few fields with a field
'ComplaintTime'

I created following function to return ShiftID in a query


Function ReturnShift(cTime)
ReturnShift= DLookup("ShiftID", "Shifts", "TimeFrom>=#" & cTime & "# AND
TimeTo<=#" & cTime & "#")
End Function

But no values return, I know there is a big problem in my 'Shift' table; In
ShiftID '2' 'TimeFrom' is Greater than 'TimeTo'!!!!

How to resolve it?
Please help.

Regards,
JB

 

[Golfinray]

Try datediff("h",[timefrom],[timeto])

 

[Clifford Bass]

Hi,

How about you have two entries for shift 2? May require the addition
of another field to your primary key.

ShiftID TimeFrameNumber TimeFrom TimeTo
1 1 11:00:00 AM 06:59:59 PM
2 1 07:00:00 PM 11:59:59 PM
2 2 12:00:00 AM 01:59:59 AM
3 1 02:00:00 AM 10:59:59 AM

Then use:

ReturnShift= DLookup("ShiftID", "Shifts", "#" & cTime & "# between TimeFrom
and TimeTo")

That should be equivalent to your DLookup.

Clifford Bass

 

[Ken Sheridan]

Its important to understand that there is no such thing in Access's date/time
implementation as a 'time value' per se, only date/time values. When a value
is entered into a column of date/time data type as a time only, what is in
fact being entered is the time on 30 December 1899, which is day-zero in
Access's date/time implementation. The values are in fact stored as a 64 bit
floating point number with the integer part representing the days and the
fractional part the times of day.

So, if the values in the table have been entered as times, and are therefore
really times on 30 December 1899, but the value passed into the function as
the cTime argument is not in fact a time on that date, e.g. if the Now()
function has been used to obtain the current time, then the value won't be
between any of the pairs of date/time values in the Shifts table. You should
be able to cover this by extending the function a little:

Function ReturnShift(cTime)

Dim strCriteria As String

If Not IsNull(cTime) Then
strCriteria = "TimeFrom >= #" & TimeValue(cTime) & _
"# And TimeTo <= #" & TimeValue(cTime) & "#")

ReturnShift= DLookup("ShiftID", "Shifts", strCriteria)
End If

End Function

The TimeValue function returns a value with the time of day of the value
passed into it, but on 30 December 1899. By default date/time values on 30
December 1899 are displayed as the time only, so it looks like a time value,
but is really a date/time value, just as the values in your Shifts table are.

Ken Sheridan
Stafford, England

 

[Beetle]

Here is another option you could consider.

The data you posted would mean that your first shift is 8 hours, your second
is 7 hours and your third is 9 hours. Is that correct, or is it just a typo?

If it is the latter, and all your shifts should be 8 (7:59:59) hours, then I
would
not store the TimeTo at all, just calculate it. So your table data would
look like;

ShiftID TimeFrom
1 11:00:00 AM
2 7:00:00 PM
3 2:00:00 AM

Then, assuming you are storing these as Date/Time values, you calculate
the end time with;

DateAdd("s", 28799, [TimeFrom])

And I wouldn't necessarily use a function to find the ShiftID, just use
the DLookup directly in the query like;

DLookup("ShiftID", "Shifts", "#" & ComplaintTime & "# Between [TimeFrom] And
DateAdd('s', 28799, [TimeFrom])")

In some instances, where you might be displaying the end time, Access
may want to display a date as well for your second shift (because it assumes
you are going into the next calendar day). You can solve that with;

TimeValue(DateAdd("s", 28799, [TimeFrom]))

If your original post was correct, and you do have varying shift lengths, I
would still calculate the end time, but it will be a bit more complicated
because you'll need to use embedded IIf statements to get the correct
end time based on the start time. Or you may be able to use a function
for that.

Just a thought, there may be other reasons why you need to actually store
the end time.

 

[Jahanzaib]

Thnak you guys!

I have resolved my problem by creating this function;

Function RetShiftID(cTime)
Dim pID
Dim pTimeFrom
Dim pTimeTo

If IsNull(DLookup("ShiftID", "Shifts", "TimeFrom>TimeTo")) = True Then
RetShiftID = DLookup("ShiftID", "Shifts", "#" & cTime & "# between TimeFrom
AND TimeTo")
Else
pID = DLookup("ShiftID", "Shifts", "TimeFrom>TimeTo")
pTimeFrom = DLookup("TimeFrom", "Shifts", "ShiftID=" & pID & "")
pTimeTo = DLookup("TimeTo", "Shifts", "ShiftID=" & pID & "")

If (cTime >= pTimeFrom And cTime <= #11:59:59 PM#) Or (cTime >= #12:00:00
AM# And cTime <= pTimeTo) Then
RetShiftID = DLookup("ShiftID", "Shifts", "ShiftID=" & pID & "")
Else
RetShiftID = DLookup("ShiftID", "Shifts", "#" & cTime & "# between TimeFrom
AND TimeTo AND (ShiftID<>" & pID & ")")
End If
End If
End Function

Basically this function check in which shift the 'DATE' is changing and
break the timeperiod in two then check the shiftID.
Clifford Bass's idea is about similar with it but it does not bound user to
break shift in 2.

Thanks again.

Here is another option you could consider.

The data you posted would mean that your first shift is 8 hours, your second
is 7 hours and your third is 9 hours. Is that correct, or is it just a typo?

If it is the latter, and all your shifts should be 8 (7:59:59) hours, then I
would
not store the TimeTo at all, just calculate it. So your table data would
look like;

ShiftID TimeFrom
1 11:00:00 AM
2 7:00:00 PM
3 2:00:00 AM

Then, assuming you are storing these as Date/Time values, you calculate
the end time with;

DateAdd("s", 28799, [TimeFrom])

And I wouldn't necessarily use a function to find the ShiftID, just use
the DLookup directly in the query like;

DLookup("ShiftID", "Shifts", "#" & ComplaintTime & "# Between [TimeFrom] And
DateAdd('s', 28799, [TimeFrom])")

In some instances, where you might be displaying the end time, Access
may want to display a date as well for your second shift (because it assumes
you are going into the next calendar day). You can solve that with;

TimeValue(DateAdd("s", 28799, [TimeFrom]))

If your original post was correct, and you do have varying shift lengths, I
would still calculate the end time, but it will be a bit more complicated
because you'll need to use embedded IIf statements to get the correct
end time based on the start time. Or you may be able to use a function
for that.

Just a thought, there may be other reasons why you need to actually store
the end time.

--
_________

Sean Bailey

Hi,
I have a table named 'Shifts', having 3 fields n with this data.

ShiftID TimeFrom TimeTo
1 11:00:00 AM 06:59:59 PM
2 07:00:00 PM 01:59:59 AM
3 02:00:00 AM 10:59:59 AM

And I have another table named "Complaints", having few fields with a field
'ComplaintTime'

I created following function to return ShiftID in a query


Function ReturnShift(cTime)
ReturnShift= DLookup("ShiftID", "Shifts", "TimeFrom>=#" & cTime & "# AND
TimeTo<=#" & cTime & "#")
End Function

But no values return, I know there is a big problem in my 'Shift' table; In
ShiftID '2' 'TimeFrom' is Greater than 'TimeTo'!!!!

How to resolve it?
Please help.

Regards,
JB