Selecting Time Range in Parameter Query

[MsLady]

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

 

[MsLady]

anybori?

 

[MsLady]

Can i get some help around here?
















Please...i beg you...anybody, somebody please help?

 

[Jon K]

See the attached database. The subform is based on the query.
.

 

[MsLady]

See the attached database. The subform is based on the query.
.

Great! Thank Jon. this is exactly what i am looking for. Thanks for taking the time to set it up. very nice of you

Yours work fine when i run it. Including the function.
But this part of my query

fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] 
Or IsNull([Forms]![frmOperatorWorkDone]![cboShift])

keeps bringing up the error "This expression is typed incorrectly, or it is too complex to be evaluated For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

When i take out that part it works fine. This works properly, so it shows that the function collects the right time value.

shift: fShiftWorked([tblTimeLog].[timeStart])

Do you have any idea know why im getting this error on the cboShift parameter?

 

[MsLady]

I separated this field from my major query [timeStart] and made a query from it.
Now, I keep getting data mismatch when i run it. It's obiviously something to do with my combo box.

What can i possibly be doing wrong?

SELECT tblTimeLog.jobId, tblTimeLog.timeLogId, fShiftWorked([tblTimeLog].[timeStart]) AS shift
FROM tblTimeLog
WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] 
Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));

 

[MsLady]

See the attached database. The subform is based on the query.
.

Just wanted to thank you again and to let you know i've found the reason why mine wasn't working. Obiviously i had nulls in there.

Got the fix here from pbaldy:
Passing parameters thru combo selection: ERROR: Data type mismatch criteria expression

Thank you!