Nested IIf with two null fields

[vicki.wells]

I'm trying to make a query that combines the data from three tables
that have data of varying ages. The newer tables don't have all the
data from the older one included in them. I need a nested IIf that
pulls the most current data, depending on whether a field is null or
not. Does that make sense? I've been using the IsNull function, but I'm
having trouble with the last part where it looks for the fields in
Tables 1 and two to both be null before using the third.

If there's data in Table1 MGR, use that.
If Table1 MGR is null, use Table2 MGR
If Table1 MGR and Table2 MGR are both null, use Table3 MGR

Can someone help>

 

[James A. Fortune]

I'm trying to make a query that combines the data from three tables
that have data of varying ages. The newer tables don't have all the
data from the older one included in them. I need a nested IIf that
pulls the most current data, depending on whether a field is null or
not. Does that make sense? I've been using the IsNull function, but I'm
having trouble with the last part where it looks for the fields in
Tables 1 and two to both be null before using the third.

If there's data in Table1 MGR, use that.
If Table1 MGR is null, use Table2 MGR
If Table1 MGR and Table2 MGR are both null, use Table3 MGR

Can someone help>

It sounds like you want:

IIf(Not IsNull(Table1.MGR), Table1.MGR, IIf(Not IsNull(Table2.MGR),
Table2.MGR, Table3.MGR))

James A. Fortune
(e-mail address removed)

 

[vicki.wells]

That's close but it's still not picking up the Table 3 data when both 1
and 2 are empty.

 

[BruceM]

Seems that it should work. What happens if you use =Table3.MGR in an
unbound text box? For possible future reference, where have you placed the
expression? In a text box? VBA?

 

[vicki.wells]

I'm using it as a calculated field in a query.

 

[BruceM]

In that case, what happens if you use the following as the calculated field:
YourField: [Table3].[MGR]
I'm assuming that Table3 is included in the query, and that MGR is a field
in that table

 

[James A. Fortune]

I'm using it as a calculated field in a query.

Vicki,

Is Table3 included in the query?

James A. Fortune
(e-mail address removed)

 

[vicki.wells]

yes

 

[BruceM]

An answer to my previous question would be helpful. So would including the
text of the message to which you are replying.

 

[vicki.wells]

I sent a reply, I don't know what happened to it.
This was your message:
In that case, what happens if you use the following as the calculated
field:
YourField: [Table3].[MGR]
I'm assuming that Table3 is included in the query, and that MGR is a
field
in that table

I'm not sure what it's supposed to do, but it gives me exactly what is
in the field [Table3.MGR].
What I'm having trouble with is getting the expression to look for the
blank fields in both Table1 and 2 and then using Table 3. I'm getting
blanks when the first two are null.

 

[BruceM]

It is supposed to give you what is in the field MGR in the table Table3. I
was looking for confirmation of that. If something doesn't work it's often
a good idea to break it into its component parts. Rather than a nested IIf
statement, you could try TestField: IIf(Not
IsNull([Table1.MGR],[Table1.MGR],"Two or three"). This will let you see if
the expression gets past evaluating Table1.MGR as NotNull.
Another possibility for troubleshooting is to use brackets thus (usually
they are added automatically as needed, but there could be something going
wrong there):
IIf(Not IsNull([Table1.MGR]), [Table1.MGR], IIf(Not IsNull([Table2.MGR]),
[Table2.MGR], [Table3.MGR]))
Or you could try it with brackets around both the table name and the field
name:
IIf(Not IsNull([Table1].[MGR]), etc.
Or you could substitute text for a field name:
IIf(Not IsNull([Table1.MGR]), [Table1.MGR], IIf(Not IsNull([Table2.MGR]),
[Table2.MGR], "Not One or Two"))
This will let you see if the expression ever produces that last result.
Another possibility is that if you have zero-length strings in the MGR
fields I believe that they will not evaluate as Null. You would need
IIf([Table1.MGR] <>"",etc.
It should work. Some systematic evaluation should point to the reason it
doesn't.

 

[James A. Fortune]

It should work. Some systematic evaluation should point to the reason it
doesn't.

That's good advice.

Vicki,

As a step toward isolating the problem, does this run correctly:

SELECT IIf(Not IsNull(Table3.MGR), 'Not Null', 'Null') AS MGR3, ... rest
of query;

How about:

SELECT IIf(Table3.MGR IS NOT NULL, 'Not Null', 'Null') AS MGR3, ... rest
of query;

James A. Fortune
(e-mail address removed)

 

[vicki.wells]

Thanks for all the advice, everyone. I finally got it to work, there
must be something funky with the data in Table3. I got it to work by
using a separate query. I did the first two table comparisons in one
query, then used that result in a final query. I compared it to table
3's data with =" " instead of IsNull or IsEmpty. Voila, it works!