How do I query by checkbox selection?

[slkasulke]

I am working on an advanced search form where users can filter product types by the various categories selected in the checkboxes provided. Right now I am working off of the design of a previous search form built which works fine. Here is the code I have running in the background:

Private Sub btnSearchAdvCrit_Click()
    Me.subAdvProductSrch.Form.RecordSource = "SELECT * FROM qryAdvProductSrch " & BuildFilter
End Sub
 
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    varWhere = Null

        If Me.Category <> "" Then
            varWhere = varWhere & "[Category] LIKE """ & Me.Category & "*"" AND "
        End If
        
        If Me.TypeOfTool <> "" Then
            varWhere = varWhere & "[TypeOfTool] = """ & Me.TypeOfTool & """ AND "
        End If
        
        If Me.ProductType <> "" Then
            varWhere = varWhere & "[ProductType] = """ & Me.ProductType & """ AND "
        End If
        
        If Me.Audience <> "" Then
            varWhere = varWhere & "[Audience] = """ & Me.Audience & """ AND "
        End If
        
        If Me.Status <> "" Then
            varWhere = varWhere & "[Status] = """ & Me.Status & """ AND "
        End If
        
        If Me.Language <> "" Then
            varWhere = varWhere & "[Language] = """ & Me.Language & """ AND "
        End If
        
        If Me.Format <> "" Then
            varWhere = varWhere & "[Format] = """ & Me.Format & """ AND "
        End If
        
    'Here is my attempt at adding checkbox criterea...    
        If Me.Earthquake = -1 Then
            varWhere = varWhere & "[Earthquake] = """ & Me.Earthquaket & """ AND "
        End If

    If IsNull(varWhere) Then
        varWhere = ""
        Else
        varWhere = "WHERE " & varWhere
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    BuildFilter = varWhere
    
End Function

 

[slkasulke]

I understand these forums can be kind of slow, but is anybody available to help?

 

[John Big Booty]

I think you Earthquake section should look like;

    'Here is my attempt at adding checkbox criterea...    
        If Me.Earthquake = -1 Then
            varWhere = varWhere & "[Earthquake] = 'Earthquake'" 
        End If

 

[slkasulke]

I think you Earthquake section should look like;

    'Here is my attempt at adding checkbox criterea...    
        If Me.Earthquake = -1 Then
            varWhere = varWhere & "[Earthquake] = 'Earthquake'" 
        End If

Thank you for the reply.

So I'm guessing if I want to add the AND, below would be the proper syntax?

If Me.Earthquake = -1 Then
            varWhere = varWhere & "[Earthquake] = 'Earthquake'" & " AND "
End If

If this is the case, then I am running into yet another issue. I do not receive and error, but when I filter, the subform returns no values, and there are indeed a few records with "earthquake" selected. Any ideas?

 

[slkasulke]

Well, I'm playing around with this a little bit and now that I changed it to the code below it does something completely different-- Access prompts me for a value for Me.Earthquake and when I type -1, the subform filters perfectly. I feel like I'm getting closer at least...

        If Me.Earthquake = -1 Then
            varWhere = varWhere & "[Earthquake] = Me.Earthquake " & " AND "
        End If

 

[John Big Booty]

What is the data that is held in field [Earthquake]

 

[slkasulke]

It is a yes/no field located in tblDisasterType.

 

[slkasulke]

Erm... nevermind. I guess I just needed a break. Got it figured out (I think).

        If Me.Earthquake = -1 Then
            varWhere = varWhere & "[Earthquake] = true " & " AND "
        End If

 

[John Big Booty]

Good work