Query by Form for multiple fields in Access 2010

Question

I want to create a query which takes field parameters through a form. For this, I created a form with combo boxes and drop down options to select the values from, this populates a text value in the respective invisible text fields whose default value I have set to null. Now in my query I give criteria for column as iif(isNull([Forms]![Conditions]![text_on_form]), [column_in_table], [Forms]![Conditions]![text_on_form]). I have done this for all the columns on which the where clause comes from the form. I have tried running this. The results seem to be random. It worked for three columns, but when I played around with it, it was giving me empty result set. Can anyone tell me what I am doing wrong? Or if there is a better way to implement query by form in Access.

Answer 1

It sounds like you are trying to create dynamic SQL. Here is the method in vba I generally prefer:

Dim SQL As String

SQL = "SELECT tblName.* From tblName WHERE (1=1)"

If Not IsNull(Me.combo1) Then
    SQL = SQL & " And ([Field1] Like ""*" & Me.combo1 & "*"")" ' I am using like statements here, but that is because this is a search tool.
End If

If Not IsNull(Me.combo2) Then
    SQL = SQL & " And ([Feild2] Like ""*" & Me.combo2 & "*"")"
End If

Docmd.RunSQL SQL

End Sub

Basically, add on to the SQL statement only if the user has put a value into your text box/ combo box or whatever. The "Where (1=1)" is to account for a situation where all fields are null.

Play with this concept to create your SQL statements. Avoid using invisible text boxes to store data, it generally means you are doing something wrong and will get mixed results (someone else on this forum can explain better than me why that is).