Return All Results When Criteria Field Is Blank

[batowl]

I am trying to build a simple query that allows my end users to search the database on one to three fields. I would like the end user to be able to leave one of these three fields blank in order to return all the possible results for that field. How do I specify that in the criteria section of Design view of the query? Thank you.

[RayMilhon]

Each row of the criteria section is = to an OR So lets say in the first criteria row you have your 3 columns set up as below. Bear in mind that the below works if any 1 of the 3 columns are not entered. Hope the formatting looks the same after I post this as it does now. Column1 Column2 Column3Row1 under Criteria =[param1] = [param2] =[param3]Row2 under Criteria =[param1] =[param2] isnull([param3]) or [param3]= ""Row3 under Criteria =[param1] =isnull([param2]) or [param2] = "" =[param3]Row4 under Criteria =isnull([param1]) or [param1] = "" =[param2] =[param3]

[RayMilhon]

Nope it doesn't

[June7]

Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially Build a Custom Filter in Your Form in Access Forms: Tips & Techniques section.

[kagoodwin13]

The best thing to do would be to create a search form, or a "query by form."

I'm going to use the fields Name, Address, and Phone for example.

With your query open, create a form. Go into Design mode, and delete the current text boxes (they will have information filled into them). Make three new unbound text boxes.

Name the first text box Name, the second text box Address, and the third text box Phone. Each should still stay unbound. Fill in the labels for ease of use. Save the form as SearchForm.

Go back to your query, and open it in design mode. Copy the following into each corresponding field's Criteria box:

Like "*" & [Forms]![SearchForm]![Name] & "*" And Like "*" & [Forms]![SearchForm]![Name] & "*"
Like "*" & [Forms]![SearchForm]![Address] & "*" And Like "*" & [Forms]![SearchForm]![Address] & "*"
Like "*" & [Forms]![SearchForm]![Phone] & "*" And Like "*" & [Forms]![SearchForm]![Phone] & "*"

This code allows you to enter as much (or as little) search criteria as you like. I can put "Mike" in the Name field, and it will return the addresses and phone numbers for all people named Mike. I can enter in just an area code in the Phone field to return all names and addresses within that area code. I can also enter in "Mike" in area code 615 to return all addresses and phone numbers for Mikes in the Nashville area. This search is as flexible as you want it to be.

Save the query. Go back to the form in design view, and add a button. When the button wizard appears, click Miscellaneous -> Run Query. Select your query, and hit Finish. Your button will now appear. After entering information into the form, you hit the button to run the query.

Save the form, and go back into form view to test it out.

Obviously "Name/Address/Phone" may not be your fields, but substitute Field1/2/3 appropriately. Let me know if you have issues. There are other ways to go about doing this, but I've been doing this exact process in my own database a lot recently. This is the only code I know that produces the desired result. It even works when using 15 search fields on tables with 10,000+ records.

If you have some data missing from the query, make sure each cell has some sort of data in it. Having null cells will sometimes cause Access to "pass over" the record when spitting back query results.

[recyan]

Just check if below gives some guidelines :

https://www.accessforums.net/showthr...3-Search-Query

Thanks

[kagoodwin13]

Just check if below gives some guidelines :

https://www.accessforums.net/showthr...3-Search-Query

Thanks

Using the following string alleviates the problem that OP was having:

Like "*" & [Forms]![SearchF]![Field Name] & "*" 
And 
Like "*" & [Forms]![SearchF]![Field Name] & "*"

"Or IsNull" should be used sparingly, especially when you have numerous search fields. Each IsNull statement creates a matrix to check every permutation of possible field entries against the IsNull field, which creates a huge mess of a query.

For instance, if batowl was to use all IsNull statements on his three fields, it would look like this:

• Fields 1, 2, and 3 are null
• Field 1 is null, 2 and 3 are not
• Field 2 is null, 1 and 3 are not
• Field 3 is null, 1 and 2 are not
• Fields 1 and 2 are null, 3 is not
• Fields 1 and 3 are null, 2 is not
• Fields 2 and 3 are null, 1 is not

So for three fields, your query is checking seven conditions. Not very efficient. If you try to expand this out to a search form with 15 possible fields (like I did), you'll end up crashing your query and locking up Access (like I did).

That's why I use the wildcard statements. I'm not a programmer, and I don't know SQL, but logically to me, I think it's easier for the computer to check a wildcard than it is to go through permutations.

[kagoodwin13]

please ignore

[batowl]

Hi kagoodwin13,

That seems to be working. But I would like to use combo boxes rather than text boxes and that does not seem to work. Should that be throwing everything off?

[kagoodwin13]

Hi kagoodwin13,

That seems to be working. But I would like to use combo boxes rather than text boxes and that does not seem to work. Should that be throwing everything off?

I couldn't get this query string to work with combo boxes either. It never made sense why manually entering text into a box will draw results, but selecting the exact same text from a combo box would not.

I might use IsNull statements instead. Since your search form only three fields, it shouldn't cause a problem.

Put your criteria as the following (for each field respectively):

[Forms]![SearchForm]![Field1] OR [Forms]![SearchForm]![Field1] IsNull
[Forms]![SearchForm]![Field2] OR [Forms]![SearchForm]![Field2] IsNull
[Forms]![SearchForm]![Field3] OR [Forms]![SearchForm]![Field3] IsNull

That should play nice with combo-boxes.