Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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!
3 Solutions
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=  '  "
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?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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
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!

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now