Struggling when there are no records

[Tara]

Forgive me...I know this has been covered extensively, but I still can't get
it quite right. I have a query that is currently set up like this:

FFAmended: IIf([tblAmended].[FF]=0,[tblContact].[FF],[tblAmended].[FF])

But what I really need it to do is to choose the data from tblContact if
either A) the value in the tblAmended is 0 or B) there is no corresponding
record in tblAmended.

Both tables are related via a ContactID field.

I know this should be simple and I've looked at several posts covering the
NZ function, but I can't seem to apply it correctly to my situation. I'm
sure it's the syntax I've been trying.

 

[KARL DEWEY]

This should work but remember you will need to be using a left join to the
[tblAmended] --
FFAmended: IIf([tblAmended].[FF]=0 OR [tblAmended].[FF] Is
Null,[tblContact].[FF],[tblAmended].[FF])

 

[John W. Vinson]

Forgive me...I know this has been covered extensively, but I still can't get
it quite right. I have a query that is currently set up like this:

FFAmended: IIf([tblAmended].[FF]=0,[tblContact].[FF],[tblAmended].[FF])

But what I really need it to do is to choose the data from tblContact if
either A) the value in the tblAmended is 0 or B) there is no corresponding
record in tblAmended.

Both tables are related via a ContactID field.

I know this should be simple and I've looked at several posts covering the
NZ function, but I can't seem to apply it correctly to my situation. I'm
sure it's the syntax I've been trying.

You need to use a LEFT JOIN in the query - you didn't post the rest of the
query so maybe you're already doing so, but you need to select the join line
from tblAmended to tblContact, select the join line, and choose the option

Show all rows in tblContact and matching rows in tblAmended

You can then change your expression to

FFAmended: IIf(NZ(tblAmended.FF) = 0, tblContact.FF, tblAmended.FF)

 

[Tara]

Perfect!

Thank you!

This should work but remember you will need to be using a left join to the
[tblAmended] --
FFAmended: IIf([tblAmended].[FF]=0 OR [tblAmended].[FF] Is
Null,[tblContact].[FF],[tblAmended].[FF])

--
Build a little, test a little.

Forgive me...I know this has been covered extensively, but I still can't get
it quite right. I have a query that is currently set up like this:

FFAmended: IIf([tblAmended].[FF]=0,[tblContact].[FF],[tblAmended].[FF])

But what I really need it to do is to choose the data from tblContact if
either A) the value in the tblAmended is 0 or B) there is no corresponding
record in tblAmended.

Both tables are related via a ContactID field.

I know this should be simple and I've looked at several posts covering the
NZ function, but I can't seem to apply it correctly to my situation. I'm
sure it's the syntax I've been trying.