IIF Is Null

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

I have 4 field columns

PAID,COMMITTED, OBLIGATED AND A NEW FIELD LABELED ECD
TOTAL:

after the label ECD Total I have the following formula


ECD Total:IIF (IsNull [PAID]+[COMMITTED])
Access error message comes up the expression you entered
contain an error.

All I'm trying to do is
If Paid =P put the obligated amount if paid = 0/null put
the committed in the field label ECD Total:



ECD TOTAL column is the logic column. I know I'm not using
excel but I need this total. Can you provide me with the
information and details.
 
If I understand your post correctly, I think the
following should work for you:

iif(Nz(PAID,0)=0,COMMITTED,OBLIGATED)

The Nz function will substitute 0 for nulls and the
result will then be compared to 0. If 0, the field will
equal COMMITTED, otherwise it will equal OBLIGATED.

Your original iif() function had a few errors, first it
did not enclose an expression in parenthesis for the
IsNull() function, second it did not have the true and
false expressions and finally it would have only found
nulls with the IsNull function, not 0's.

Hopefully that will help. Post back if I misunderstood
what you were trying to do.

-Ted Allen
 
Another couple of ideas:

ECD Total: IIF (IsNull([PAID])=False, [PAID]+[COMMITTED], [COMMITTED])


ECD Total: Nz([PAID], 0) + [COMMITTED]
 
I'm sorry but this does not work!!

If PAID field =P use OBLIGATED field
If PAID field = null use COMMITTED field

The formula will appear after the field label
ECD TOTAL: IIf

There are no fields with zero's so I don't need the Nz
function. Also, I sent a new email because the information
below does not work based on what I previously wrote.

-----Original Message-----
If I understand your post correctly, I think the
following should work for you:

iif(Nz(PAID,0)=0,COMMITTED,OBLIGATED)

The Nz function will substitute 0 for nulls and the
result will then be compared to 0. If 0, the field will
equal COMMITTED, otherwise it will equal OBLIGATED.

Your original iif() function had a few errors, first it
did not enclose an expression in parenthesis for the
IsNull() function, second it did not have the true and
false expressions and finally it would have only found
nulls with the IsNull function, not 0's.

Hopefully that will help. Post back if I misunderstood
what you were trying to do.

-Ted Allen
-----Original Message-----


I have 4 field columns

PAID,COMMITTED, OBLIGATED AND A NEW FIELD LABELED ECD
TOTAL:

after the label ECD Total I have the following formula


ECD Total:IIF (IsNull [PAID]+[COMMITTED])
Access error message comes up the expression you entered
contain an error.

All I'm trying to do is
If Paid =P put the obligated amount if paid = 0/null put
the committed in the field label ECD Total:



ECD TOTAL column is the logic column. I know I'm not using
excel but I need this total. Can you provide me with the
information and details.




.
Click to expand...
.
Click to expand...
 
Well, the formula should have worked for the condition
that you described, with the exception that it does not
specifically check for "P", it checks for Null as you
were originally trying to do. Also, it does not check
for an empty string (which is different than Null). The
Nz() function is often used to check for both Null, and
another result, such as an empty string or 0, by
substituting one of those values for the Null. But, in
your case I should have substituted an empty string
rather than a 0 since you have a text field. So it
should have been,

iif(Nz(PAID,"")="",COMMITTED,OBLIGATED)

But this would only have made a difference in the case
that you had empty strings rather than Nulls. If you
only had Nulls and "P"'s it should have worked fine.

There are other formulas that will work for you under
your second post. You can choose which will work best
depending on the likelihood of encountering values other
than "P" or Null in the future.

HTH, Ted Allen

-----Original Message-----
I'm sorry but this does not work!!

If PAID field =P use OBLIGATED field
If PAID field = null use COMMITTED field

The formula will appear after the field label
ECD TOTAL: IIf

There are no fields with zero's so I don't need the Nz
function. Also, I sent a new email because the information
below does not work based on what I previously wrote.

-----Original Message-----
If I understand your post correctly, I think the
following should work for you:

iif(Nz(PAID,0)=0,COMMITTED,OBLIGATED)

The Nz function will substitute 0 for nulls and the
result will then be compared to 0. If 0, the field will
equal COMMITTED, otherwise it will equal OBLIGATED.

Your original iif() function had a few errors, first it
did not enclose an expression in parenthesis for the
IsNull() function, second it did not have the true and
false expressions and finally it would have only found
nulls with the IsNull function, not 0's.

Hopefully that will help. Post back if I misunderstood
what you were trying to do.

-Ted Allen
-----Original Message-----


I have 4 field columns

PAID,COMMITTED, OBLIGATED AND A NEW FIELD LABELED ECD
TOTAL:

after the label ECD Total I have the following formula


ECD Total:IIF (IsNull [PAID]+[COMMITTED])
Access error message comes up the expression you entered
contain an error.

All I'm trying to do is
If Paid =P put the obligated amount if paid = 0/null put
the committed in the field label ECD Total:



ECD TOTAL column is the logic column. I know I'm not using
excel but I need this total. Can you provide me with the
information and details.




.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top