Filtering form with combo boxes

[milkman2500]

Hi,

I'm attempting to filter my form with combo boxes. I've added the below code, but the if statement that checks if one of the combo boxes is null won't work. If the combo box is null or "", it's supposed to assign a wildcard to the variable. But it doesn't pass the test and goes straight to the Else statement. I've highlighted the if statement that keeps failing in red.

Private Sub btn_Search_Click()
'create variables to store the combo box values

Dim str_Country As String
Dim str_Vendor As String
Dim str_Survey As String
 
'If any of the combo boxes are left blank, assign wild card to their value.

If cbo_Country.Value = "" Or Null Then
    str_Country = "*"
Else
    str_Country = cbo_Country.Value
End If
 
If cbo_Survey_Vendor.Value = "" Or Null Then
    str_Vendor = "*"
Else
    str_Vendor = cbo_Survey_Vendor.Value
End If
 
[COLOR=red]If cbo_Survey_Title.Value = "" Or Null Then
    str_Survey = "*"
Else
    str_Survey = cbo_Survey_Title.Value
End If[/COLOR]
 
MsgBox ("Country like " & str_Country & ", Vendor like " & str_Vendor & ", Survey like " & str_Survey)
 
'filter the form according to the combo box values.
Me.Form.Filter = "[Country] Like '" & str_Country & "'" & _
                "And [Survey_Vendor_Name] Like '" & str_Vendor & "'" & _
                "And [Survey_Name] Like '" & str_Survey & "'"
Me.FilterOn = True
End Sub 'btn_Search_Click()

 

[jdraw]

Your syntax for IF is incorrect

If cbo_Survey_Title.Value = "" Or Null Then
str_Survey = "*"
Else
str_Survey = cbo_Survey_Title.Value
End If

Nothing is equal to Null -- not even Null.

The syntax for the IF statement would be

If cbo_Survey_Title.Value = "" Or IsNull( cbo_Survey_Title) Then
    str_Survey = "*"
Else
    str_Survey = cbo_Survey_Title.Value
End If

But it isn't clear, to me at least, What exactly you are trying to do. You can set the Rowsource of a combo -- you could use a query that does not allow Nulls.

Perhaps you could tell us more about your project and database.

 

[milkman2500]

I want the user to be able to specify their search criteria if they want, but not be forced to. So if they selected a value from the drop down box, then I want that value used. However, if no value was selected, I want it to ignore that combo box. I tried using the value from the combo box as the criteria for the corresponding field in the query behind the form, but the query doesn't respond if no option is selected (which I want the option for). I'm assuming that's because it doesn't like the Null value. So here I replace that with the wildcard "*".

 

[jdraw]

Take a look at this free video tutorial and see if it helps.
http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

 

[spikepl]

Your code is a bit of a mishmash, because instead of changing criteria you only change the values, and drag pointless criteria with you into the WHERE clause.

Check out Da Master's solution to this type of problem (code is supplied in a link on the page): http://allenbrowne.com/ser-62.html