Solved

Assigning a value if IIF IsNull is not true.

Posted on 2014-01-21
7
561 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 47

Expert Comment

by:Dale Fye (Access MVP)
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 35

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 49

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

776 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