Syntax for IIf then Statement

[bethabernathy]

Hi - I have a text box on a report that where I need to perform a calculation based on criteria. I am looking to calculate this: IIf the field "desc" is null, then I want to subtract one from the dataset. So, this is where I am at:

=IIf([desc]=Null,-1)

This is returning blank, so I am obviously writing the statement incorrectly. Any information is really appreciated. Thanks so much. Beth beth@integratedresourcemgmt.com

 

[GoDawgs]

Hi Beth. First off the beginning syntax should be [desc] is null, not [desc]=null...also you're missing a piece to your iif statement...here's the syntax for iif:

iif(comparison, what happens when comparison is true, what happens when comparison is false)

Right now after you fix the is null portion you are just going to get -1 in the text box if desc turns out to be null, and if it isn't null then the text box will be blank since there is not a second set of results in the iif statement. Make sense? I'm not sure if that's what you want...and I'm also not sure what exactly you want to have happen when desc is true (like I said, right now your text box will just be -1). Be glad to help some more with a little more information, but I hope that gets you going in the right direction.

Kevin

 

[bethabernathy]

Hi Kevin - I understand what you are saying. This is where I am at:

=IIf([desc]="is null",[desc]-1)

But what I want is to get a count of "desc" -1 into the statement. So, I have tried round and round, but keep getting syntax error messages:

=IIf([desc]="is null",=Count[desc]-1)

Any ideas are really appreciated. Thanks, Beth beth@integratedresourcemgmt.com

 

[GoDawgs]

Ok, if I'm understanding you correctly you just want a count of all [desc] where it is not null. If that's the case then this would work:

=count([desc])

That should be all you need, that will count all the times that there is any value in the [desc] field. Am I following correctly?

Kevin

 

[bethabernathy]

Hi Kevin - Thanks for your help. It is really great. What I want to do is find a way to subtract 1 from the desc count if the value in the desc field is null.

There are records in the desc field that are empty. I need a way to subtract one in my count if there is a record where the desc field is not populated. If I use =Count([desc]) it still counts the empty record, unfortunately.

So, I guess what I need to figure out is how to add a calculation into an IIf statement. I was thinking of this, but it gives me an error:

=IIf([desc]="is null",[desc]=Count[desc]-1)

If the desc is null, the the count of the desc needs to be -1? Maybe it isn't possible. This is a real weird project.

Thanks Again, Beth
beth@integratedresourcemgmt.com

 

[GoDawgs]

Hmm, if that count([desc]) doesn't work then I'm guessing that those fields aren't actually null, but are actually blank text (="" i mean). Let's try dcount instead, something like this:

=DCount(&quot;[desc]&quot;, &quot;table&quot;, &quot;[desc] <> ''&quot

You need to put the name of your table or query in where it says &quot;table&quot;...and put it in quotation marks (so if the query is Recordsource then you need to replace &quot;table&quot; with &quot;recordsource&quot.

If that doesn't work, then try testing if it's null:

=DCount(&quot;[desc]&quot;, &quot;table&quot;, &quot;[desc] is null&quot

Just so you know for future projects [desc]=&quot;is null&quot; is not the right syntax, it should be [desc] is null...just like that. I hope one of these two works for you, but if not we'll keep trying.

Kevin

 

[GoDawgs]

sorry, I meant is not null for the second Dcount...like so:

=DCount(&quot;[desc]&quot;, &quot;table&quot;, &quot;[desc] not null&quot

 

[bethabernathy]

Hi Kevin - =Count[desc] does work. It just counts all the &quot;desc&quot; fields as there are other fields associated with that record set. I did the dcount and it is returning the count of all the records i.e.:

=DCount(&quot;[desc]&quot;,&quot;tblotherforgl&quot;,&quot;[desc] is null&quot
returns 0

and

=DCount(&quot;[desc]&quot;,&quot;tblotherforgl&quot;,&quot;[desc] <> ''&quot
returns 17

One of the records in description is null and I need my total to be 16.

I just can't remember how to combine the count -1 into the iif statement. I think I have seen it done before, but am not sure. Thanks you for your great help. I really really apppreciate it. Beth

beth@integratedresourcemgmt.com

 

[martinloat]

Hi beth
Try something along these lines

(IIf(IsNull([tableName]![fieldname])),([tableName]![New fieldname]-1),count([tableName]![fieldname]))AS [New Field name]

I'm not sure about the way I've put count into it but this illustrates your main question: How to treat null values in an IIf statement. Notice how the comparitor (IsNull), because it is a function, moves to the start of the table or phrase being evaluated.
Good Luck
Perry

 

[CosmoKramer]

Beth,

If I am reading this right and you are trying to simply count all non-null records, then this should work:

=DCount("desc","tblotherforgl","[desc] is not null")

CK

 

[cmmrfrds]

A NULL field is NOT counted.

If the field you are counting is null it will not be included in the count. If it is included in the count then there is some other value besides null in the field. It could be an empty field or contain spaces both are non null. You do not need to subtract -1 from the field instead if it is empty or has spaces make it null then it will not be included in the count.

iif(field=&quot;&quot;,null,count(field)) '- empty
or
iif(field=&quot; &quot;,null,count(field)) '- spaces