Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using IIF in Criteria to Filter a Null

Posted on 2011-10-17
9
Medium Priority
?
615 Views
Last Modified: 2012-05-12
I am using the query grid to add in the criteria.    The IIF statement I am using is:

IIf(Trim([State_Name])=Trim([allocations_1].[spec]),"CA",is null)

The field is a text field and returns a data type mis-match, when I am trying to filter by Is Null.  I can open up the table in its own grid and use IS NULL to filter the fields I need.

How can I query a null value using an IIF statement - since "" doesn't work.
0
Comment
Question by:jeffrey_b_lee
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 75
ID: 36982557

Try this
IIf(Trim([State_Name])=Trim([allocations_1].[spec]),"CA", IsNull([State_Name]) )
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36982685
This looks like a computed column in the query grid, so I think what you want is:

NewColName: IIf(Trim([State_Name])=Trim([allocations_1].[spec]),"CA",NULL)

The results in this column will either be "CA" or NULL, and you can then put a test in the critieria that looks like:

Criteria: "CA"
or
Criteria: IS NULL


0
 
LVL 9

Expert Comment

by:keyu
ID: 36984431
iif(isnull(Trim([State_Name])),'NULL',IIf(Trim([State_Name])=Trim([allocations_1].[spec]),"CA","NO MATCH"))

here first it will check for null

if its null than return null else compare it with variable and return some value.
other wise non match as value.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jeffrey_b_lee
ID: 36990388
None of these possible solutions worked.  I have to move on and use code to get aound this hurdle.
0
 
LVL 75
ID: 36990438
Can you upload a db and explain exactly what you need to do, relative to the exact objects in the db (tables, queries, etc).

mx
0
 
LVL 1

Expert Comment

by:pdi656
ID: 36995432
As fyed alluded to, you need to add this formula as a field in your query, not as criteria:
IIf(Trim([State_Name])=Trim([allocations_1].[spec]),"CA",NULL)

Then you can apply your criteria filter to this new field for the nulls.

I attached a small pic of what I mean.
Query formula
0
 

Author Comment

by:jeffrey_b_lee
ID: 37009220
The criteria can dynamically change from "CA" or "NULL -- based on [SPEC]
0
 
LVL 9

Accepted Solution

by:
keyu earned 1000 total points
ID: 37024423
can you please explain what exact error /problem you are facing with this code ?

iif(isnull(Trim([State_Name])),'NULL',IIf(Trim([State_Name])=Trim([allocations_1].[spec]),"CA","NO MATCH"))
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 37024591
Your original post indicated:

IIf(Trim([State_Name])=Trim([allocations_1].[spec]),"CA",is null)

There are potentially several problems with this syntax.

1.  You refer to [State_Name] but to [allocations_1].[spec].  Does your query include the table "[Allocations_1]"?  If not then you cannot simply refer to a field in that table without including that table in your query.  The only reason you would need to refer to the table name is clarity, and if you are going to do that, I'd include the table name when refering to all of the fields.

2.  Either [State_Name] or [Spec] could be NULL.  If either of these values is NULL, then the Trim function will return a NULL and the comparison of any value to a NULL will return a NULL.  So if either of those values is NULL, the IIF() expression will return the value associated with FALSE.

3.  Your reference to "is null" as the return if the comparison is false is not valid.  If you want to return a NULL value, then the syntax should be:

IIf(Trim([State_Name])=Trim([allocations_1].[spec]),"CA", NULL)

However, the final line in your original post intrigues me.  You said:

How can I query a null value using an IIF statement - since ""

To query for a NULL value in an IIF( ) function, the best method is to use either the IsNull() function, or the NZ() function.

IsNull([FieldName]) will return a boolean value (true or false) that indicates whether the value of the field  is NULL.  The other function NZ([FieldName]) by default will return the value of zero if the value of the field is NULL.  But it accepts a second (variant) parameter, which allows you to assign a return value to the function, so if you use: NZ([FieldName], "Unknown"), and the value of [FieldName] is NULL, then the function will return the string value "Unknown".

However, neither of these functions will identify empty strings "".  If you are only concerned with identifying whether a field contains no information (NULL or "") then the best way to test for this is:

[FieldName] & "" = ""

or

LEN([FieldName] & "") = 0

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question