Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

Assigning a value if IIF IsNull is not true.

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
ferguson_jerald
Asked:
ferguson_jerald
1 Solution
 
mbizupCommented:
Try this expression:

YourField:  ABS((NOT IsNull(fld1))  + (NOT IsNull(fld2)) + (NOT IsNull(fld3)) + (NOT IsNull(fld4)))
0
 
Dale FyeCommented:
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
 
mbizupCommented:
<<
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
PatHartmanCommented:
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
 
Gustav BrockCIOCommented:
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
 
Dale FyeCommented:
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
 
GozrehCommented:
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now