IIf and Is Not Null not working (1 Viewer)

[SOLVED - sort of] IIf and Is Not Null not working

I have a combo box on my form that will filter my query criteria. In my combo box, I have the selection "<All>" to allow all data to show. I put this statement in the Query criteria box:

IIf([Forms]![frm_JobList]![cbo_Category]="<All>",Is Not Null,[Forms]![frm_JobList]![cbo_Category])

If I select <All> from the combo box, the query returns no data.
If I select an alternative selection (that is in the table) then the query returns the data.

The error is obviously in the "True" part (Is Not Null). But when I use Is Not Null by itself, I get all the data.

Any thoughts as to what I am doing wrong?

try

IIf([Forms]![frm_JobList]![cbo_Category]="<All>",<>"",[Forms]![frm_JobList]![cbo_Category])

if you're using the iif statement below to create a text string that goes into a query, i would think that you would need to put quotes around "Is not null".
--------------------------------------------------------------------------------

I have a combo box on my form that will filter my query criteria. In my combo box, I have the selection "<All>" to allow all data to show. I put this statement in the Query criteria box:

IIf([Forms]![frm_JobList]![cbo_Category]="<All>",Is Not Null,[Forms]![frm_JobList]![cbo_Category])

If I select <All> from the combo box, the query returns no data.
If I select an alternative selection (that is in the table) then the query returns the data.

The error is obviously in the "True" part (Is Not Null). But when I use Is Not Null by itself, I get all the data.

Any thoughts as to what I am doing wrong?

rainman89 said:

try

IIf([Forms]![frm_JobList]![cbo_Category]="<All>",<>"",[Forms]![frm_JobList]![cbo_Category])

Click to expand...

Tried that.

mondo3 said:

if you're using the iif statement below to create a text string that goes into a query, i would think that you would need to put quotes around "Is not null".

Click to expand...

Tried that too. Though when you put quotes around it, I believe it is actually looking for the string "Is Not Null" in the field and not all records that are not null

Now if I substitute an actual selection from the combo box the query works as expected. So the error definitely seems to point to the Is Not Null within the Iif statement.
Ex:
IIf([Forms]![frm_JobList]![cbo_Category]="<All>","Atlas",[Forms]![frm_JobList]![cbo_Category]) returns all records containing Atlas

I've even tried:
IIf([Forms]![frm_JobList]![cbo_Category]="<All>",,[Forms]![frm_JobList]![cbo_Category])
thinking I might be able to leave the criteria blank.

I've also tried the reverse to no avail:
IIf([Forms]![frm_JobList]![cbo_Category]<>"<All>", [Forms]![frm_JobList]![cbo_Category],Is Not Null)

Thanks for the thoughts. Any other ideas while I go back to searching?

could you show me the entire code sentence?

try:
strSQL = "SELECT * FROM Table WHERE Category " & IIf([Forms]![frm_JobList]![cbo_Category]="<All>","Is Not Null","=[Forms]![frm_JobList]![cbo_Category]") & "'"

mondo3 said:

could you show me the entire code sentence?

try:
strSQL = "SELECT * FROM Table WHERE Category " & IIf([Forms]![frm_JobList]![cbo_Category]="<All>","Is Not Null","=[Forms]![frm_JobList]![cbo_Category]") & "'"

Click to expand...

SELECT [txt_JobID], [txt_JobCategory], [txt_JobStatus]
FROM tbl_JobTracking
WHERE ((([txt_JobCategory])=IIf([Forms]![frm_JobList]![cbo_Category]="<All>",Is Not Null,[Forms]![frm_JobList]![cbo_Category]));

I did try it with quotes around Is Not Null. When I do that it actually looks for the string "Is Not Null" in the field. It does not return all fields that are not null.

try to build it like a string as in my example. so:
strsql= "SELECT [txt_JobID], [txt_JobCategory], [txt_JobStatus] FROM tbl_JobTracking
WHERE ((([txt_JobCategory]) " & IIf([Forms]![frm_JobList]![cbo_Category]="<All>", " Is Not Null "," = [Forms]![frm_JobList]![cbo_Category]" ))

No matter how I tried this in the criteria section, it wouldn't work. But, I got it.

Instead of putting the IIf statement in the criteria section of the query design, I had to add another column in the query and add this:

Expr1: IIf([Forms]![frm_JobList]![cbo_Category]="<All>",[tbl_JobTracking].[txt_JobCategory] Is Not Null,[tbl_JobTracking].[txt_JobCategory]=[Forms]![frm_JobList]![cbo_Category])

Then I had to set the criteria to True


So the SQL looks like this:
It's very ugly, but it works.