?
Solved

Assigning a value if IIF IsNull is not true.

Posted on 2014-01-21
7
Medium Priority
?
597 Views
Last Modified: 2014-01-30
Hello Experts.

I have what I hope is a simple question.  I have some fields that I would like to add together in a query.  Some of the fields may contain null values.  I would like any field with a null value to have a value of 0, and all other fields to be assigned a value of 1.

For example:

fld1 = null therefore the value assigned would be 0
fld2 = 4 therefore the value assigned would be 1
fld3 = 2 therefore the value assigned would be 1
fld4 = 7 therefore the value assigned would be 1

The desired result of the formula is 3.

I have tried the following, but I get 13 (4+2+7) instead of 3 (1+1+1).

Sum(IIF(Not IsNull([fld1]),1,0)+IIf(Not IsNull([fld2]),1,0)+IIF(Not IsNull([fld3]),1,0)+
IIF(Not IsNull([fld4]),1,0)

Any help would be greatly appreciated.  

I am currently using MS Access 2013 on Windows 7.

Thanks,
J
0
Comment
Question by:ferguson_jerald
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39797336
Try this expression:

YourField:  ABS((NOT IsNull(fld1))  + (NOT IsNull(fld2)) + (NOT IsNull(fld3)) + (NOT IsNull(fld4)))
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39797337
Try:

SUM(-(NZ([fld1],0) > 0)-(NZ([fld2],0) > 0) - (NZ([fld3],0)>0) - (NZ([fld4],0)>0))

This basically creates boolean values (0 or -1) for each field using the >0 comparison
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39797366
<<
I have tried the following, but I get 13 (4+2+7) instead of 3 (1+1+1).
>>

From your description, it sounds like you are simply trying to add across columns (ie: horizontal sum).

The SUM function gets a total over all of the records in your table (vertical sum)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 41

Expert Comment

by:PatHartman
ID: 39797377
Your expression looks correct although people have less trouble understanding positive statements than negative ones so I would change it to:

Sum(IIF(IsNull([fld1]),0,1)+IIf(IsNull([fld2]),0,1)+IIF(IsNull([fld3]),0,1)+
IIF(IsNull([fld4]),0,1))
0
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 39797414
This should do:

YourField:  Abs([fld1] Is Not Null) + Abs([fld2] Is Not Null) + Abs([fld3] Is Not Null) + Abs([fld4] Is Not Null)

/gustav
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39797426
Please disregard my reply.  I just realized you indicated NULL = 0, any other value (including 0) = 1).  My response assumed that zeros were to be counted as zero as well.

How about:

(1-IsNull([fld1])) + (1-IsNull([fld2])) + ((1-IsNull([fld3])) + (1-IsNull([fld4]))
0
 
LVL 10

Accepted Solution

by:
Gozreh earned 2000 total points
ID: 39820888
Just remove the "Sum"

So now you can use
Or:
IIf(Not IsNull([fld1]),1,0)+IIf(Not IsNull([fld2]),1,0)+IIf(Not IsNull([fld3]),1,0)+IIf(Not IsNull([fld4]),1,0)
Or:
ABS((NOT IsNull(fld1))  + (NOT IsNull(fld2)) + (NOT IsNull(fld3)) + (NOT IsNull(fld4)))
Or:
Abs([fld1] Is Not Null)+Abs([fld2] Is Not Null)+Abs([fld3] Is Not Null)+Abs([fld4] Is Not Null)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

601 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