Filter records in a sub form from multiple combo boxes in a main form

[jdashm]

Been working on this for a while.
Found some code that is partially working. I have 4 combo boxes in the main form that i want to use to filter records in a sub-form.
It works for the first two combo boxes and i thinks it's because they are both text data. The other two consist of a number and the other a date. When i try to include one or both of these fields, the error i get is "Run-Time Error 3709" / "The search key is not found in any record".

I currently have the code designating the variables (strJobNo and strTrainDate) as String types. When i make them a Byte and the other a Date type i get a data mismatch error when i try to filter.
Appreciate any help to resolve this.

Here is the code that i currently have written...
================================================== ================================================== =========================
Private Sub cmdApplyFilter_Click()




Dim strCourseTitle As String
Dim strRosterNumber As String
Dim strJobNo As String
Dim strTrainDate As String


If IsNull(Me.cboTraining.Value) Then
strCourseTitle = "Like '*'"
Else
strCourseTitle = "='" & Me.cboTraining.Value & "'"


End If


If IsNull(Me.cboRosterNum.Value) Then
strRosterNumber = "Like '*'"
Else
strRosterNumber = "='" & Me.cboRosterNum.Value & "'"
End If


If IsNull(Me.cboJobNumber.Value) Then
strJobNo = "Like '*'"
Else
strJobNo = "='" & Me.cboJobNumber.Value & "'"
End If


If IsNull(Me.cboTrainingDate.Value) Then
strTrainDate = "Like '*'"
Else
strTrainDate = "='" & Me.cboTrainingDate.Value & "'"
End If




Me.fsbTrainingRosters_New.Form.Filter = " [strTR_CourseTitle]" & strCourseTitle & " AND [strTR_RosterNumber]" & strRosterNumber & _
" AND [bytJobNumber]" & strJobNo & " AND [dtmTR_TrainingDate]" & strTrainDate
Me.fsbTrainingRosters_New.Form.FilterOn = True




End Sub
================================================== ================================================== =========================

[pbaldy]

For starters, date data types need # as a delimiter instead of ', and numeric values don't need any delimiter (more accurately shouldn't have a delimiter). If you still have problems, use a variable and this to see how the finished filter is coming out:

http://www.baldyweb.com/ImmediateWindow.htm

[jdashm]

Been working with the delimiters and can't quite get the right syntax.
Appreciate it if you could show me what it should look like based on the code i sent.
Thanks in advance.

[pbaldy]

strTrainDate = "=#" & Me.cboTrainingDate.Value & "#"
strJobNo = "=" & Me.cboJobNumber.Value

[pbaldy]

By the way, personally I'd build up a single string that only included the fields/values the user had entered.

[moke123]

As Paul suggests, you can construct a single filter string for the items selected like the following...

If Not IsNull(Me.cboTraining) Then

strFilter = strFilter  & "strTR_CourseTitle = " & Me.cboTraining & " and "
End If

If Not IsNull(Me.cboRosterNum) Then
strFilter = strFilter  & "strTR_RosterNumber = """ & Me.cboRosterNum & "'" And "
End If

''''Do the rest of them in the same format

If Nz(strFilter,"") <> "" then
strFilter = Left(strFilter, Len(strFilter) -4)   'trim off the trailing "And "
End if

adjust delimiters as needed.

edit:for some reason the code above is getting split into 2 code boxes?