Query by Form for multiple fields in Access 2010

0
votes

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.

2
ms-accessms-access-2007
I don't know what's wrong with the above solution, but I have created a workaround for it. I am creating a new column for every column in where clause and define it like this: new_column: [field] = [Forms]![Conditions]![text_on_form] or [Forms]![Conditions]![text_on_form] is null This seems to solve the problem, but I would like to know why the previous method was not working. And also I would like to know how I can compare check boxes from the form with yes/no columns in the table using the second method. Any help is appreciated – amir shadaab

2 Answers

0
votes

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).

-1
votes

Just use the Like operator. Put this in your criteria field in the query Like "\*" & Forms![Form_Name]![Form_Field] & "\*" -- This tells it to get anything if the field is blank (or null) and matches whatever you have in the field. This may not be what you want. It should be noted that it will return anything with the text string in it. For example: if you type "the" it will return tether, these, theses, thermometer (anything with the word "the" in it. It works best for multi word or longer strings that can be matched more accurately, however it works for a search query because there is usually a set of human eyes looking for the result and erroneous results is not a huge problem.