Struggling when there are no records

  • Thread starter Thread starter Tara
  • Start date Start date
T

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.
 
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])
 
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.
Click to expand...

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)
 
Perfect!

Thank you!

KARL DEWEY said:
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.


Tara said:
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.
Click to expand...
Click to expand...
 
Back
Top