Using multiple parameters in a query

Hello experts -
I'm working on a search form in Access 2000 that will allow a user to enter a single criteria or multiple criteria to search on. I have a form with 4 fields. I used the query expression builder to  reference these fields in the query grid. However, the query does not return any records unless all 4 criteria are filled in. I looked through the knowlege base and couldn't find anything that wasn't overly complicated for my level of experience. Is there an easy way to get this query to run when there are less than all 4 criteria filled in? Thanks for any help you can give!
Who is Participating?
I would guess that in the query builder, you have your criteria specified as:


To keep it simple and make it work how you desire, try ammending your criteria like so

IIF(IsNull( (([FormName]![FormCriteriaField]) OR ([FormName]![

I believe what is happening is that if nothing is entered in the criteria field, the query requires that that field be empty...  What you want is to ignore that criter if it's not entered right?

Hope that helps
This difficult to explain but in the query design view you need to use the Or method to do this.  But put your parameter input one cell down from the other:

Example grid:

Field:                Field1                              Field2                           Field3                             Field4

Criteria:            [Enter search text 1]        
       Or:                                                    [Enter search text 2]
       Or:                                                                                      [Enter search text 3]
       Or:                                                                                                                            [Enter search text 4]

Hope this is clear
Also, to do a search based on user input you'd have to use code to build the query and then open a report based on the query:

Dim strPrintCriteria as string

If Isnull([TextBox1]) then [TextBox1] = ""
If Isnull([TextBox2]) then [TextBox2] = ""
If Isnull([TextBox3]) then [TextBox3] = ""
If Isnull([TextBox4]) then [TextBox4] = ""

strPrintCriteria = "field1='" & [Textbox1] & "' OR field2='" & [Textbox2] "' OR field3='" & [Textbox3] & "' OR field4='" & [Textbox4] & "'"

DoCmd.OpenReport "YourReportName", acPreview, , strPrintCriteria

Make sure and put the apostrophes as indicated in the strPrintCriteria because your criteria are strings. ie; "field1= '  "  & "  '  OR field2=  '  "
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Normally, criteria are ANDed, not ORed.  If ORed, each criteria would add to the number of returned records, rather than narrowing the field.  I have found if you use the query builder and make reference to textboxes on a form and make the default value of the textbox "*", then by joining each criteria with AND and referring to all four criteria as follows, you can get there:

Select * from myTable where
fld1=forms!myForm!myTbx1 AND
fld2=forms!myForm!myTbx2 AND
fld3=forms!myForm!myTbx3 AND

I believe this approach will narrow down the field as you are able to complete more criteria.  Is this what you want?
MavisleeAuthor Commented:
Sorry it took so long to get back to this. I was actually trying each of your suggestions and couldn't get anything to work so I tried to simplify this whole thing by putting in less criteria. I thought Ryan DeMougin's was the simpliest to implement, but for some reason the wildcard is not working in the iif statement. Instead of four options, I put in beginning and end dates (which works fine) and a single combo box.The query getting criteria from the combobox looks like this:

iif(isNull(Forms![frm_SearchEvent]![cboPatient]),"*", Forms![frm_SearchEvent]![cboPatient])

I get nothing back if cboPatient is null,but I do get the correct record if it's not.  Does anyone have any more ideas? If not, I'm going to create 2 different queries and call each one based on the criteria input.
MavisleeAuthor Commented:
Since I didn't hear anything back from anyone, I did a workaround. I created  separate queries, each dependent on the data inputted. If all criteria is filled in, one query is called. If only some criteria is filled in, another query is called. Arji's solution didn't work because I was trying to limit the number of records returned. GrayL's solution was close, but I had a problem getting it to work. Again, I think it was the * in the expression builder. I also RyanDeMougin's and had the same problem. Still I thank everyone for responding and I'm awarding at least some points to each for your effort. Thanks again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.