Search doesn't like yes/no

[SueBK]

We have a search form which has combo boxes from which criteria can be chosen to search i.e. Project, category etc (most based on a primary key of "ID").

We want to add check boxes for 2 items - safety Yes/No and environment Yes/no - so that the form can be searched on these criteria also. They have been added as Check38 and Check40 in the code below.

The code is only returning one record with safety when there are many more, so I believe the argument is wrong although I can’t see it.

The clear function clears the search results and resets the combo boxes, to enable the user to start a new search without exiting the form. It currently clears the combo boxes and check boxes but doesn’t clear the search results anymore.

(Apologies for the lengthy code; scared if I truncate it I might delete the issue

FILTER

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
 
    varWhere = Null  ' Main filter
    
    ‘Check for ProjectID
    If Me.cmboProjectID > 0 Then
        varWhere = varWhere & "[ProjectID] = " & Me.cmboProjectID & " AND "
    End If
    
    ‘Check for Issue Category
    If Me.cmbIssueCategoryID > 0 Then
        varWhere = varWhere & "[CategoryID] = " & Me.cmbIssueCategoryID & " AND "
    End If
    
    ' Check for Issue Priority
    If Me.cmboIssuePriorityID > 0 Then
        varWhere = varWhere & "[PriorityID] = " & Me.cmboIssuePriorityID & " AND "
    End If
    
    ' Check for Issue Resolution Status
    If Me.cmbResolutionStatusID > 0 Then
        varWhere = varWhere & "[StatusID] = " & Me.cmbResolutionStatusID & " AND "
    End If
    
    'Corridor
    If Me.cmbCorridorGroupID > 0 Then
        varWhere = varWhere & "[CorridorGroupID] = " & Me.cmbCorridorGroupID & " AND "
    End If
    
    'Schedule
    If Me.cmbScheduleID > 0 Then
        varWhere = varWhere & "[ScheduleID] = " & Me.cmbScheduleID & " AND "
    End If
    
    'Project Manager
    If Me.cmbProjectManagerID > 0 Then
        varWhere = varWhere & "[ProjectManagerID] = " & Me.cmbProjectManagerID & " AND "
    End If
    
    'Project Owner
    If Me.cmbProjectOwnerID > 0 Then
        varWhere = varWhere & "[ProjectOwnerID] = " & Me.cmbProjectOwnerID & " AND "
    End If
[B]If Me.Check38 = -1 Then[/B]
[B]        varWhere = varWhere & "([SafetyCategory] = True) AND "[/B]
[B]    ElseIf Me.Check38 = 0 Then[/B]
[B]        varWhere = varWhere & "([SafetyCategory] = False) AND "[/B]
[B]        End If[/B]
    
    
    [B]If Me.Check40 = -1 Then[/B]
[B]        varWhere = varWhere & "([EnvironmentCategory] = True) AND "[/B]
[B]    ElseIf Me.Check40 = 0 Then[/B]
[B]        varWhere = varWhere & "([EnvironmentCategory] = False) AND "[/B]
[B]    End If[/B]
    
    
 ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
     End If
  
    BuildFilter = "SELECT * FROM qryAllIssuesData " & varWhere
 
End Function

CLEAR ALL SERCH ITEMS

Private Sub btnClear_Click()
    Dim intIndex As Integer
    
    ' Clear all search items
    Me.cmboProjectID = 0
    Me.cmbIssueCategoryID = 0
    Me.cmboIssuePriorityID = 0
    Me.cmbResolutionStatusID = 0
    Me.cmbCorridorGroupID = 0
    Me.cmbScheduleID = 0
    Me.cmbProjectManagerID = 0
    Me.cmbProjectOwnerID = 0
    Me.Check38 = 0
    Me.Check40 = 0
    
    End Sub

SEARCH FUNCTION

Private Sub btnSearch_Click()
Me.FrmSubIssuesSearch.Form.RecordSource = BuildFilter()
Me.FrmSubIssuesSearch.Requery
End Sub

 

[Galaxiom]

Problems like this are usually down to some problem dealing with Nulls.

Note that with two state controls like checkboxes you don't really need the if.
A fixed clause will give you same result as testing and setting.

"[SafetyCategory] = [Forms]![formname].[Check38] AND "

 

[DCrake]

If you change it where Field <> True this should handle Null values or you can wrap Nz() around the yes/no fields. Such as:

(Nz([EnvironmentCategory],0) = True)

David