Using Like "*" or is Null in IIF Statement

[TT_CAM]

Hi
I've a problem with trying to use a user input field on a form as a criteria for a query. I've used checkboxes as indicators for the fields that will be used to run the query. The query criteria/syntax works for fields in the table that have no NULL values but for fields that do have NULL values, I can't quite seem to figure out how to get the query to do what I want. I've attached a simplified version of what I'm trying to accomplish. In the example, there is one table with three fields: Book_Title, Book_Edition and ISBN. I want users to be able to search for the title of a book and get results if there is an Edition or a NULL Value as well as get the specific results if the user inputs the title of the book and the Edition.

EDIT:
I made an error in uploading the wrong sample accdb, I've attached the correct one in my post below

 

[plog]

I want users to be able to search for the title of a book and get results if there is an Edition or a NULL Value as well as get the specific results if the user inputs the title of the book and the Edition.

Whether its english or computer code, you really need to be careful with how you write ANDs and ORs. The above sentence I quoted of yours is extremely hard to parse because of them. I honestly can't tell what you want because of the AND/ORS--I don't know which set of clauses goes with which others. I suspect you might have multiple issues with your code based on something similar. I can't see it specifically, but I am guessing it might exist.

What I do see is an error in constructing your like:

{1} Like IIf([Forms]![Search]![Edition_1]=-1,[Forms]![Search]![Edition],{2}(([MOCK_DATA].[Book_Edition]) {3} Like "*" {4} Or ([MOCK_DATA].[Book_Edition]) Is Null))

{1}. You can't put logic inside a LIKE.

{2}. What is this? You have a field then another Like? I nor the computer can parse this.

{3}. Now you have another LIKE nested inside the first like but this one is in the 'else' argument of the If. It's an inception like headeache. I have no idea where I am at or what is being evaluated.

{4} Now there's an OR? What does this correspond to? Are we inside the LIKE string or inside the IIf logic?

With all that said, I vote you scrap this method. Instead, you should build a report based on your query--without any criterai. Then you open that report via a DoCmd.OpenReport method (https://msdn.microsoft.com/en-us/library/office/ff192676.aspx). With that method you can pass it a filter string so that the report opens to just the records you want to see based on your form. This way you can take as many lines as you need and properly write out your filter string instead of trying to cram it all into one criteria line of a query.

 

[TT_CAM]

Ok let me try to clarify if you look at the form there are two textboxes for user input and a option toggle. What I'm trying to accomplish is a query that will run using criteria selectively based on the toggle.
When the user inputs the Book Title but leaves the option unchecked and no input in the Edition, the query should run returning all values related to that Book Title (i.e. * and Null in the Edition column);
When the user inputs the Book Title AND the option checked with input in the Edition field, the query should run specifically based on the input Book Title and Edition. Hope that clears it up a bit.

{1}. You can't put logic inside a LIKE.

I'm pretty sure you can as this criteria works perfectly for several other fields, the only caveat is this particular case is this field has NULL values which I'm trying to compensate for. For another field

Like IIf([Forms]![Search]![Checkbox]=-1,[Forms]![Search]![Date_Input], Like "*")

works perfectly, that much I can assure you.

{2}. What is this? You have a field then another Like? I nor the computer can parse this.

I actually did not have this code, Access added the fields to the code when I switched to SQL view. My original code looked more like:

Like IIf([Forms]![Search]![Edition_1]=-1,[Forms]![Search]![Edition],( Like "*" Or Is Null))

{3}. Now you have another LIKE nested inside the first like but this one is in the 'else' argument of the If. It's an inception like headeache. I have no idea where I am at or what is being evaluated.

It works, and I can prove it. The objective is to pass a specific value to the query, either the data that is specified via input or to get ALL the records in the result.

{4} Now there's an OR? What does this correspond to? Are we inside the LIKE string or inside the IIf logic?

The OR is in the Like string, and is trying to return all the results ("*" or NULL values). This is the part that doesn't work.

I can't use a report as thats not what I'm trying to accomplish.

 

[sneuberg]

I believe

Like IIf(IsNull([Forms]![Search]![BookName]),[Book_name],"*" & [Forms]![Search]![BookName] & "*")

is what you are looking for. This is tested in the attached database. Note that I changed the textbox name from Name to BookName. Since "Name" is a property of nearly every object in Access it's a real bad choice for a control name.

 

[TT_CAM]

I believe

Like IIf(IsNull([Forms]![Search]![BookName]),[Book_name],"*" & [Forms]![Search]![BookName] & "*")

is what you are looking for. This is tested in the attached database. Note that I changed the textbox name from Name to BookName. Since "Name" is a property of nearly every object in Access it's a real bad choice for a control name.

Hi Steve, thanks for the attempt but this checks for NULL values in the BookName on the form whereas i want the criteria passed to be

Like "*" or is Null

for the Edition
By itself it works but it does not inside of the falsepart/truepart of the IIF statement.

EDIT:
I see I made an error in uploading the wrong sample accdb. Here is the correct one.

 

[sneuberg]

It looks like you want to see all of the records if the checkbox is uncheck. In that case just pass the field name in the false part of the IIF so that the WHERE clause ends up something like:

Mock_Data.Book_Edition Like Mock_Data.Book_Edition

which is always true and produces all of the records. This

Like IIf([Forms]![Search]![Edition_1]=-1,[Forms]![Search]![Edition],[MOCK_DATA].[Book_Edition])

implements that idea.

 

[TT_CAM]

Thanks again for the assistance Steve but unfortunately this doesn't return the NULL values

 

[plog]

Can you use some sample data to demonstrate what you want to occur? Use this format:

Book Name Input, Book Edition Input, Number of Records to show in query:
User Interf, NULL, ?
User Interface Design, NULL, ?
User, 5, ?

Fill in the question marks above and then add more to demonstrate all cases.

 

[sneuberg]

Maybe I got it this time. This time I just wrote out the SQL where clause so that it would be true when record are desired in the output. That SQL is

SELECT MOCK_DATA.ISBN, MOCK_DATA.Book_Edition, MOCK_DATA.Book_name
FROM MOCK_DATA
WHERE (MOCK_DATA.Book_name Like "*" & Forms!Search!Title & "*") And ((Forms!Search!Edition_1 <>False And Forms!Search!Edition= [Book_Edition]) Or Forms!Search!Edition_1=False);

Since Forms!Search!Edition_1 is a boolean value I use it directly in this logic. This might look a little strange in the grid view but if you stare at it for a while it might make sense. The attached database has this query. Note that I needed to give the Edition_1 checkbox a default value of false otherwise this didn't work as expected when the form was first loaded.

Do you really need/want this checkbox? Wouldn't it be better if the presence or absence of a value in the Edition textbox determine this? How about a combo box for this that has as its row source the distinct values of the Book_Edition?

 

[TT_CAM]

That works exactly as I need it!!! Thanks a million!!
Took quite a while to reply as my laptop HD decided to die but this is exactly what I was trying to achieve!!!

Do you really need/want this checkbox? Wouldn't it be better if the presence or absence of a value in the Edition textbox determine this? How about a combo box for this that has as its row source the distinct values of the Book_Edition?

This Library database was just a sample (couldn't post work data for obvious reasons) so in the context I need it for, a combo box won't work. I had trouble with determining if the absence of a value in the textbox was "" or NULL.

 

[sneuberg]

I attached a database with a version of the query that eliminates the checkbox. If the Edition textbox is left empty it acts as though the checkbox were uncheck. The SQL of this query is:

SELECT MOCK_DATA.ISBN, MOCK_DATA.Book_Edition, MOCK_DATA.Book_name
FROM MOCK_DATA
WHERE (((MOCK_DATA.Book_name) Like "*" & [Forms]![Search]![Title] & "*") AND ((IsNull([Forms]![Search]![Edition]))=False) AND (([Forms]![Search]![Edition])=[Book_Edition])) OR (((MOCK_DATA.Book_name) Like "*" & [Forms]![Search]![Title] & "*") AND ((IsNull([Forms]![Search]![Edition]))<>False));

If you look at this in grid mode you will see that the state of the checkbox is just replaced with

IsNull([Forms]![Search]![Edition])