Selecting Time Range in Parameter Query

[sisieko]

I have a value list combobox (cboShift) on my form with selections as "1st Shift";"2nd Shift";"3rd Shift" and a subform that should filter my records based on the shift selected on the mainform.

My data fields in my query are [TimeStart], [Time Stop], [other fields].
I would like that e.g. when "1st Shift" is selected, it should filter my records based on the [timeStart] that falls within the range, and so on.

I really don't know how to handle this, even to set the time range parameter for this...
I have written this function for a start. To continue, i ask for your help or guide. Please help????

Function fShiftWorked(strTimeStart As DateTime)
Dim strOperatorStart As String
strOperatorStart = FormatDateTime(([tblTimeLog]![timeStart]), vbLongTime)
If strOperatorStart >= #8:00:00 AM# And strOperatorStart < #5:00:00 PM# Then
        strTimeStart = "1st Shift"
ElseIf strOperatorStart >= #5:00:00 PM# And strOperatorStart < #12:00:00 AM# Then
        strTimeStart = "2nd Shift"
ElseIf strOperatorStart >= #12:00:00 AM# And strOperatorStart < #8:00:00 AM# Then
        strTimeStart = "3rd Shift"
Else
        strTimeStart = "2nd Shift"
End If
End Function

 

[Golom]

I'm always leery of trying to make time comparisons using strings.

Function fShiftWorked(TimeStart As Date) As String
Dim OperatorStart As Date
Dim TimeStart     As String

OperatorStart = TimeValue([tblTimeLog]![timeStart])

If OperatorStart >= TimeValue("8:00:00 AM") And _
   OperatorStart <  TimeValue("5:00:00 PM") Then
        TimeStart = "1st Shift"

ElseIf OperatorStart >= TimeValue("5:00:00 PM") And _
       OperatorStart <  TimeValue("12:00:00 AM") Then
        TimeStart = "2nd Shift"

ElseIf OperatorStart >= TimeValue("12:00:00 AM") And _
       OperatorStart <  TimeValue("8:00:00 AM") Then
        TimeStart = "3rd Shift"

Else
        TimeStart = "2nd Shift"
End If

fShiftWorked = TimeStart

End Function

Or possibly

Function fShiftWorked(TimeStart As Date) As String
Dim OperatorStart As Integer

OperatorStart = Hour([tblTimeLog]![timeStart])

If OperatorStart <  8 Then:      fShiftWorked = "3rd Shift"
ElseIf OperatorStart < 17 Then:  fShiftWorked = "1st Shift"
Else:                            fShiftWorked = "2nd Shift"
End If

End Function