Ignore an empty combobox

Question

I have 5 combo boxes, used to filter a listbox. I want to make it so not all 5 have to be selected in order for the filter to work. Each combobox starts blank ( "" ), and I want to be able to skip 1 or several and filter with the ones I select. Here is the AfterUpdate code for one of the comboboxes:

On Error Resume Next

    'These are the comboboxes
   Me.BilletMaterial.RowSource = [MechanicalData.Billet Material]
   Me.BilletNumber.RowSource = [MechanicalData.Billet Number]
   Me.TestType.RowSource = [MechanicalData.Test Type]
   Me.Axis.RowSource = [MechanicalData.Axis]
   Me.Temperature.RowSource = [MechanicalData.Temperature]

   Me.BilletMaterial.Requery    
   Me.BilletNumber.Requery
   Me.TestType.Requery
   Me.Axis.Requery
   Me.Temperature.Requery
'Listbox
Me.DataView.RowSource = "SELECT [Billet Material],[Billet Number], " & _
"[Specimen],[Temperature],[Modulus],[Ultimate Tensile Strength],[Strain]  " & _
"FROM MechanicalData " & _
"WHERE 1=1 " & _
"AND [MechanicalData.Axis] = '" & Me.Axis.Value & "'  " & _
"AND [MechanicalData.Test Type] = '" & TestType.Value & "' " & _
"AND [MechanicalData.Temperature] = " & Temperature.Value & " " & _
"AND If (Not IsNull(BilletNumber.Value)) Then [MechanicalData.Billet Number] = " & BilletNumber.Value & " End if  " & _
"AND [MechanicalData.Billet Material] = '" & BilletMaterial.Value & "' "

My first thought would be to implement multiple "if is not empty..." statements in the WHERE portion of the SELECT statement, but I'm not sure how to do that or even if it's possible.

I know the syntax of this line is wrong.

"AND If (Not IsNull(BilletNumber.Value)) Then [MechanicalData.Billet Number] = " & BilletNumber.Value & " End if  " & _

Why not build the WHERE string using If (Not IsNull(Combo)) Then strWhere = strWhere & — Fionnuala
@Remou is 'strWhere = strWhere' in your comment equivalent to [MechanicalData.Axis] = '" & Me.Axis.Value & "' " (etc) in my code? — user3241316
Yes, just build up the where statemet. You might like to start with WHERE 1=1 so everything else can be AND this=that — Fionnuala
@Remou I added a line of code following your suggestion to the bottom of my post. I am not too familiar with proper syntax yet, so if you could steer me in the right direction for it that would be very helpful. — user3241316
I don't think you're utilizing the string concatenation properly. It should look something like AND [MechanicalData.Axis] = " & Me.Axis.Value & " .. Single quotes are not needed. Has this actually worked before? Also, when I check for Null values in comboboxes, I used the method Remou described (except I use If IsNull (Object.Value) = False Then) — Mark C.

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2014-04-10T21:12:00

I believe the code you have above is missing the 'FROM MechanicalData' clause. Using the suggestions from above, the following should work (assuming all combobox values are non-numeric):

Dim strWhere    As String

strWhere = " WHERE (1=1)"
If Not IsNull(Axis.Value) Then strWhere = strWhere & " AND ([MechanicalData.Axis] = '" & Axis.Value & "') "
If Not IsNull(TestType.Value) Then strWhere = strWhere & " AND ([MechanicalData.Test Type] = '" & TestType.Value & "') "
If Not IsNull(Temperature.Value) Then strWhere = strWhere & " AND ([MechanicalData.Temperature] = '" & Temperature.Value & "') "
If Not IsNull(BilletNumber.Value) Then strWhere = strWhere & " AND ([MechanicalData.Billet Number] = '" & BilletNumber.Value & "') "
If Not IsNull(BilletMaterial.Value) Then strWhere = strWhere & " AND ([MechanicalData.Billet Material] = '" & BilletMaterial.Value & "') "

Me.DataView.RowSource = "SELECT [Billet Material],[Billet Number], " & _
        "[Specimen],[Temperature],[Modulus],[Ultimate Tensile Strength],[Strain] " & _
        "From MechanicalData " & _
        strWhere

Ignore an empty combobox

2 Answers