Using multiple parameters in a query

Posted on 2005-04-16
Last Modified: 2006-11-18
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!
Question by:Mavislee
    LVL 17

    Expert Comment

    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
    LVL 17

    Assisted Solution

    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=  '  "
    LVL 44

    Assisted Solution

    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?
    LVL 2

    Accepted Solution

    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

    Author Comment

    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.

    Author Comment

    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now