Multiple ComboBox parameters

I hate to admit I'm terrible at syntax when it comes to writing SQL statements for VBA with criteria in multiple fields (WHERE F1 = criteria1 AND F2 = criteria2 AND...), ESPECIALLY when the criteria are based on Comboboxes. SO, I'll ask for some help if you don't mind please.

I have 3 ComboBoxes (unbound) on Form1 with other bound controls to display my results. Each unbound ComboBox allows 1 selection in their respective list of values
I believe this is going to be like an excel 'Filter in place' functionality
Lets say we have Combo1(DataType Text), Combo2(DataType Number), Combo3 (Datatype Date)

I want to select the dataset from Table_Main based on the values in the comboboxes.

Here's what I'm hoping for but I need the correct syntax

SELECT * FROM Table_Main WHERE MYTEXT = LIKE *Me!Combo1.value* AND MYNUMBER = LIKE *Me!Combo2.value* and MYDATE = LIKE *Me!Combo3.Value*

I used LIKE so that empty ComboBoxes would be ignored and return a recordset using just the other criteria or return ALL records if nothing is selected

I found an example for 1 combobox in the Help but the SYNTAX baffles me because they broke everything into multiple lines like:

Sub cmboCompanyName_AfterUpdate()
Dim strNewRecord As String
strNewRecord = "SELECT * FROM Customers " _
& " WHERE CustomerID = '" _
& Me!cmboCompanyName.Value & "'"
Me.RecordSource = strNewRecord
End Sub

instead of a single line SQL string. For some reason I find the above impossible to decipher even though copying and pasting it it works.

How do I get the 3 criteria in a single SQL statement for use in VBA?

Thanks in advance!
Goh

1. Why are you using LIKE? If you are selecting from a combo box then it is EQUALS (=) that you should be using.

2. The syntax would be:

One line:
strSQL = "SELECT * FROM Table_Main WHERE MYTEXT =" & Chr(34) & Me.Combo1 & Chr(34) & " AND MYNUMBER = " & Me.Combo2 & " AND MYDATE = #" & Me.Combo3 & "#"

Multiple lines:
strSQL = "SELECT * FROM Table_Main WHERE MYTEXT =" & Chr(34) & Me.Combo1 & Chr(34) & _
" AND MYNUMBER = " & Me.Combo2 & _
" AND MYDATE = #" & Me.Combo3 & "#"

So if I don't select anything in one of the combo boxes will that be a problem?

GohDiamond said:

So if I don't select anything in one of the combo boxes will that be a problem?

Click to expand...

Yes, it would as it is written. Is this in VBA? If it is I would suggest this modification to keep it cleaner (more code but cleaner than having the Or Is Null stuff in the SQL String):

strWhere = strWhere & "MYDATE = #" & Me.Combo3 & "# AND"

Click to expand...

I found that there needs to be a space after the final AND so it looks like

strWhere = strWhere & "MYDATE = #" & Me.Combo3 & "# AND "

otherwise the -5 eats into the date.

Thanks Bob, you're awesome!

Cheers!
-Goh