Return nothing if Query criteria is Null (1 Viewer)

[KyleB]

I see the opposite in searches, but I can't find this particular setup. What I've got is a form with 8 fields which I use for my criteria in my query. If someone searches for a phrase and it matches exactly I don't have a problem, and I'm able to do a keyword search as well.

The problem I'm encountering is if there is no word at all. If the user leaves a cell blank (any of them) then the default entry which the Query enters into the search is *. This results in the user getting a list of all items in the entire table, because the search finds and matches everything. What I'm trying to figure out is how do I ignore a criteria if the value is Null? Currently my Iif Statements just aren't working right. I either get everything in the table or nothing at all.

IIf(IsNull([Forms]![Search Form]![Item Name]),Null,Like "*" & [Forms]![Search Form]![Item Name] & "*")

This returns null whether or not the form has data in that text box. I've tried multiple variations and permutations on the Iif statement with different Is Null, Not Is Null, etc trying to get the correct result, but nothing so far.

Originally I was using just this:

Like "*"&[Forms]![Search Form]![Item Name]&"*"

This would work fine as long as the text box actually had a value in it. If on the other hand the box was left blank then it automatically assumes a "*" was entered and it pulls up all records because they all match the criteria, and I don't want that.

Kyle B.

[This message has been edited by KyleB (edited 04-24-2002).]

 

[RV]

Look here:
http://www.access-programmers.co.uk/ubb/Forum3/HTML/003533.html

Come back if you need further assistance

RV

 

[KyleB]

That doesn't give the result I'm looking for though. Unless I'm reading it wrong. Basically what I'm trying to do is this:

If there is a value in the text box then do a search for it, if no value then do no search.(or return no values)

That example returns what I'm currently seeing; meaning everything, which is not what I'm attempting to achieve.

Kyle B.

Incidently, this search criteria could contain values in 1, several, or all 8 cells, and I only want to search the table for those cells that actually contain information.

[This message has been edited by KyleB (edited 04-24-2002).]

 

[RV]

The post I referred to earlier was meant to help you on your way as it's dealing with the same matter.
Otherwise look here:
http://www.access-programmers.co.uk/ubb/Forum4/HTML/000827.html

Greetings,

RV

 

[KyleB]

Found the solution.

Using a Like criteria you can search for a keyword. Make an additional expression at the end of the query where you can do this:

In column one under Criteria:
Like "*" & [Forms]![Search Form]![Item Name] & "*"

In Column two, reference the form, and cell, and set criteria to "Is Not Null"

Result:
WHERE ((([Baking Data].[Item Name]) Like "*" & [Forms]![Search Form]![Item Name] & "*") AND (([Forms]![Search Form]![Item Name]) Is Not Null))

This way it checks your form for the existence of a string in the cell. If a value is present it will use that criteria for the search. If the cell is blank then it assumes null, but because of your second condition it will not show any results.

Therefore you can do a search for a specific string but if you don't want any search results for that particular criteria it won't list anything.

[This message has been edited by KyleB (edited 04-24-2002).]

[This message has been edited by KyleB (edited 04-24-2002).]