problem with iff statement and is null


K

Kemi

Hello all:

In my query, I am pulling some fields from an existing table. The
fields I am pulling include: Transaction In State Since date and
Logged Date. I want access to provide me with Transaction in State
Since Date, but if Transaction in State Since date is null or blank I
want access to replace those cells with the Logged Date, if the logged
date is also blank when the transaction date is blank, then it should
just give me whatever is in the Transaction in State Since cell (in
this case, a blank or null cell). I used the following iff statement:
(=IIf(IsNull([Invoice StateJan 14-April30]![Transaction in State
Since]),[Invoice StateJan 14-April30]![Logged Date],[Invoice StateJan
14-April30]![Transaction in State Since]);

But I noticed without the iif statement, I had about 13,919 records,
after I used the iff statement I had about 9197 records.

Does anyone know if there is something wrong with the iff statement or
if I should not be using an iff statement in the first place, please
tell me what to use instead.

Thanks a lot for your patience.


opeyemi1
 
Ad

Advertisements

A

Andrew L.

Kemi

You need a nested Iif statement, which evaluates one statement within another:-

=Iif(IsNull([FIELD1],Iif(IsNull([FIELD2],[FIELD1],[FIELD2]),[FIELD1])

HTH
Andrew L.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Join Or IFF Statement? 1
Null Criteria Problem 4
Subject: Need help on Iff Statement 1
Access Property sheet data missing also syntax 0
IFF causing a prompt in a query 1
iif is not null query 2
#error - Blank 3
IIf statement 2

Top