Append query with "if" on possible missing field.

[Rob - IT Data Services]

I want to do an append query from table-1 to table-2 but the Table-1 may be
missing a field [Rep] if so I want to pick up the [Rep] from table-3

Im appending to a field [Rep] in table-1

So far Ive tried building an update query with table-1 & table-3 at the top
appending to Table-2
then in the boxes at the bottom - tried

Exp: iif(nz(isnull([table-1]![Rep])),[table3]![Rep],[Table1]![Rep])

but all this does when there is no [Rep] field in table-1 is prompt of the
variable input..

Anythoughts would be much appreciated.?

 

[Jeanette Cunningham]

Hi Rob,
here is a 2 step way to do it.
Append the data from table 2 to table 1.
Now to fill in the blanks in table 1, append from table 3 to table 1 but
only for those records where [rep] is null.
Others may be able to give you a 1 step process.

Jeanette Cunningham

 

[Jeanette Cunningham]

Oops, that second query should be an update query, not an append query.

Jeanette Cunningham

 

[Klatuu]

I think the nz and isnull together as you are using them may be the problem:

Try
Exp: Nz([Table-1]![Rep], [Table3]![Rep])