Getting #Error in linked query

[Ziggy1]

I have 2 tables linked in a query.....

TransActionDate REC-ClosedDate
26-May-10
27-May-10 27-May-10
27-May-10 #Error
27-May-10 #Error
27-May-10 #Error
27-May-10 #Error
27-May-10 #Error
27-May-10
27-May-10
27-May-10
27-May-10


[ProductMovement] is the main table and the join is set to return all records and only the matching from [qryRecStat] query

I've tried the following, but still get #Error in the fields where there is no matching record?

REC-ClosedDate: Format(IIf([actv_type]="REC",IIf([qryRecStat].[TransActionDate]="","",[qryRecStat].[TransActionDate]),""),"dd-mmm-yy")


REC-ClosedDate: Format(IIf([actv_type]="REC",iif(isnull([qryRecStat].[TransActionDate]),"",[qryRecStat].[TransActionDate]),""),"dd-mmm-yy")


I didn't want to create another query, how can I deal with this?

 

[pbaldy]

The records where there are no match will return Null, not "". Either use the Nz() function or IsNull() in your formula.

 

[Ziggy1]

thanks, I had that on the second one, but I guess I need to add a second one to the main expression.