Iif Statement Not Returning Null Values (1 Viewer)

haideeo

New member
Local time
Today, 06:33
Joined
Apr 16, 2020
Messages
3
I am using a query by form to return results, but I am not able to retrieve the null values. I have tried a number of things:

Without using the QBF the following query provides the results that I am expecting:
Like "*" Or Is Null

When I add this to my IIF query I get no results. My query is:
Like IIf(IsNull([Forms]![frmSearch]![BDR]),Like "*" Or Is Null,"*" & [Forms]![frmSearch]![BDR] & "*")

If I remove the null portion of my query I get all of the BDRs that are populated, which is part of the way to the results I would like to see:
Like IIf(IsNull([Forms]![frmSearch]![BDR]),"*","*" & [Forms]![frmSearch]![BDR] & "*")

What is the proper syntax to retrieve the BDRs that are populated as well as the null values?

Thank you for your assistance.
 

plog

Banishment Pending
Local time
Today, 05:33
Joined
May 11, 2011
Messages
11,613
You need a left and right side of every comparison. You can't just say OR and expect the computer to know what you are comparing IS Null to. Reference the field again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:33
Joined
Oct 29, 2018
Messages
21,358
Hi. What exactly did you want to happen if the form is empty, return all records? Or, something else?
 

haideeo

New member
Local time
Today, 06:33
Joined
Apr 16, 2020
Messages
3
You need a left and right side of every comparison. You can't just say OR and expect the computer to know what you are comparing IS Null to. Reference the field again.
This is what I am not getting right. So I tried both of the following and still retrieved nothing:
Like IIf(IsNull([Forms]![frmSearch]![BDR]),Like "*" Or [BDR] Is Null,"*" & [Forms]![frmSearch]![BDR] & "*")
Like IIf(IsNull([Forms]![frmSearch]![BDR]),Like "*" Or IsNull([BDR]),"*" & [Forms]![frmSearch]![BDR] & "*")

Can you provide an example?
 

Micron

AWF VIP
Local time
Today, 06:33
Joined
Oct 20, 2018
Messages
3,476
I think you need to take a closer look at the IIF function. It translates to
If A, Then B, else C; each comma denotes a section - test,true part, false part

This is probably being interpreted as your test
(IsNull([Forms]![frmSearch]![BDR])

and this is your True part
Like "*" Or [BDR] Is Null

and this is your False part
"*" & [Forms]![frmSearch]![BDR] & "*"

I'm guessing that's not what you intended. Also, Is Null is for SQL, IsNull is for VBA and you're mixing them in the same place.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:33
Joined
Feb 28, 2001
Messages
27,001
Code:
Like IIf(IsNull([Forms]![frmSearch]![BDR]),Like "*" Or [BDR] Is Null,"*" & [Forms]![frmSearch]![BDR] & "*")

Let's take a look at what you REALLY asked.

First, an IIF has three parts: A test or criterion argument that must resolve to either TRUE or FALSE, followed by a value argument to use if the test returns TRUE, followed by a value argument to use if the test returns FALSE.

Part one of that IIF is IsNull([Forms]![frmSearch]!BDR) which will resolve to TRUE or FALSE because the IsNull only returns TRUE or FALSE. So that part is good.

Part two says Like "*" OR [BDR] Is Null which will resolve to the logical OR of LIKE "*" and [BDR] IS NULL - which includes a syntactically incomplete clause, LIKE "*" and a complete clause. The problem that the compiler will have is, WHAT is LIKE "*" - i.e. you have nothing against which to compare anything. Something must be like something else. Further, the [BDR] IS NULL part is redundant since your test entirely is based on [BDR] being null.

Part three says "*" & [Forms]![frmSearch]![BDR] & "*" - which essentially brackets the contents of [BDR] between two asterisks.

So if the code runs, part two will return a logic (Boolean) value if it returns anything at all, because of the OR operator. Part three would return a string. Then there is that "LIKE" that is outside the IIF.

I smell a search form that will look for anything or that will look for anything that contains [BDR] contents. I'm going to take a wild-eyed guess that you really want something similar to this:

LIKE IIF( IsNull( [Forms]![frmSearch]![BDR] ), "'*'", "'*" & [Forms]![frmSearch]![BDR] & "*'" )

Which will reduce to either LIKE '*' or LIKE '*{contents of BDR}*'
 

Users who are viewing this thread

Top Bottom