B
bu
I have a query that contains a table and that table is left joined to
another query (ie., show me records in table even if they do not exist in
the query). The result set includes fields from both the table and the
other query. The problem is that #Error displays in the cells for the query
fields that don't have a match and using eval functions like IsNull,
IsEmpty, IsError, IsMissing don't work. A sample is below where A is the
table and B is the query:
SELECT A.[Accounting ID], A.[Invoice #], A.[Invoice Date], B.[AuthNbr]
FROM A LEFT JOIN B ON (A.[Invoice Date] = B.[Invoice Date]) AND (A.[Invoice
#] = B.[Invoice #]) AND (A.[Accounting ID] = B.[Accounting ID])
WHERE A.[Accounting ID]="CUST"
The B.AuthNbr column returns #Error when the join doesn't hit?! I would
actually like it to be an IIF condition like the following:
IIf( IsNull(B.[AuthNbr]),"MYAUTH",B.[AuthNbr])
But I can't do that as the #Error is throwing everything off...
Any thoughts?
tia,
b
another query (ie., show me records in table even if they do not exist in
the query). The result set includes fields from both the table and the
other query. The problem is that #Error displays in the cells for the query
fields that don't have a match and using eval functions like IsNull,
IsEmpty, IsError, IsMissing don't work. A sample is below where A is the
table and B is the query:
SELECT A.[Accounting ID], A.[Invoice #], A.[Invoice Date], B.[AuthNbr]
FROM A LEFT JOIN B ON (A.[Invoice Date] = B.[Invoice Date]) AND (A.[Invoice
#] = B.[Invoice #]) AND (A.[Accounting ID] = B.[Accounting ID])
WHERE A.[Accounting ID]="CUST"
The B.AuthNbr column returns #Error when the join doesn't hit?! I would
actually like it to be an IIF condition like the following:
IIf( IsNull(B.[AuthNbr]),"MYAUTH",B.[AuthNbr])
But I can't do that as the #Error is throwing everything off...
Any thoughts?
tia,
b