A LEFT join will not work the way you want it to when you have criteria against the right-side table. To force Access to return the results you want, you need two queries.
q1
Selects ONLY the right-side table using the criteria you need.
q2
Uses a left join from MatrialNo to q1 with NO criteria.
A subquery will probably work but they are difficult to build with QBE.