problem with iff statement and is null

[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

 

[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.