filtering doesnt work with left join 1

[Ashank4vba]

Hi,

I created a left join from table A to table B based on a common field (All records from A and only matching records from B).

I have a text field in B which I am trying to use to filter the records containing blank or Null values in that field.
Although using 'Is Not Null' or '<> "" ' works while using table B without the join (or using an inner join, or right join), it doesnt work while using left join from A to B.
However strangely (for me), if I use Len(<field name here>) <> "". It seems to work.

Can anyone tell me why plz?

Thanks.

-cheers
Ashank

 

[PHV]

To filter records on the right side of a left join you have to use an embedded view:
SELECT ...
FROM A LEFT JOIN (
SELECT ... WHERE the filter here
) AS L ON A.SomeField=L.SomeField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886