nz function

[callaway]

I am having trouble with a query which uses the results of sub queries for it's data.

I have included an extract below:

SELECT DISTINCT qryItemNumber.item, IIf(NZ([qryComposition2].[DESCRIPTOR_VALUE])<>"","FOUND","NOT FOUND") AS Expr1
FROM qryComposition2, qryItemNumber;

What should happen is that if the qryComposition2 has a value (qryItemNumber always has a value)the result from the query should be Expr1="FOUND".
Otherwise if the qryComposition2 returned null Expr1="NOT FOUND"

What is basically happening is that whenever qryComposition2 has a null value the query using it as a sub query fails.

Any suggestions.

Hope above is clear and thanks in advance.

Regards

 

[Rich]

Try IIf(IsNull([qryComposition2].[DESCRIPTOR_VALUE])"FOUND","NOT FOUND")