Option Group to Search

[jamjarr]

Hello All

Talk about frustrating and I am sure the answer will be easy for some.

I have a Main form with search criteria and a subform with the data. See the attached image for the layout. The data comes from a query.

I have a yes/no field called "CompletedP" that I want to use to search on. This field is to filter in/out completed jobs. The Frame is called the generic name "Frame60". I am using an option group with the choice of yes or no plus other search criteria using combo boxes. The combo box filtering is fine but the option box does not appear to form any part of the search. To search I select any of the boxes and select "Search".

I post the code here.

Any assistance would be very much appreciated.

Private Sub cmdSearch_Click()
On Error Resume Next
Dim strFilterSQL As String
Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "


'This code is for a specific search where you will need to enter the exact string
'The source for this code can either be from a table or query
If Me![Location] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub6.Location = """ & Location & """"
End If

If Me!

 <> "" Then
            sCriteria = sCriteria & " AND qrySearchCriteriaSub6.Code like """ & Code & "*"""
        End If

        If Me![ClientCode] <> "" Then
                sCriteria = sCriteria & " AND qrySearchCriteriaSub6.ClientCode Like """ & ClientCode & "*"""
        End If

        If Me![ProjectCode] <> "" Then
                sCriteria = sCriteria & " AND qrySearchCriteriaSub6.ProjectCode = """ & ProjectCode & """"
        End If

        If Me![StartDate] <> "" And EndDate <> "" Then
            sCriteria = sCriteria & " AND qrySearchCriteriaSub6.DateAllocated between #" & Format(StartDate, "dd-mmm-yyyy") & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
        End If

       Select Case Me.Frame60.Value
        Case 1
            strFilterSQL = sSql & " Where [CompletedP] = -1;"
        Case 2
            strFilterSQL = sSql & " Where [CompletedP] =  0;"
        Case Else
            strFilterSQL = sSql & ";"
    End Select

        If Nz(DCount("*", "qrySearchCriteriaSub6", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
        sSql = "SELECT DISTINCT [JobID],[Location],[Premises Details],[ProjectCode],[Code],[ClientCode],[DateAllocated],[CompletedP],[FileNumber] from qrySearchCriteriaSub6 " & sCriteria
        Forms![frmSearchCriteriaMain6]![frmSearchCriteriaSub6].Form.RecordSource = sSql
        Forms![frmSearchCriteriaMain6]![frmSearchCriteriaSub6].Form.Requery
        Else
        MsgBox "The search failed find any records" & vbCr & vbCr & _
        "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
        End If
        
    
End Sub
[/QUOTE]

 

[MarkK]

You've assigned your Frame60 result to "strFilterSQL" which you never use. Your result here should be assigned to "sCriteria" if it is to be included in your search.

 

[jamjarr]

Thanks very much it works.

However it does not interact with the other search criteria. So if I chose a particular Staff and set the option group to Yes it will show all Staff that have the jobs completed.

Almost there.