Access Outer Join "Where" Criteria Question

C

cranberryconsult

Hi All,

I have an outer join that has a where condition on it. The problem is
that where the subordinate table has no records, the where clause seems
to be changing the query so that it doesn't bring back a row. For
example, if I take the where clause off of the query, I get a row for
Student XYZ even if they have no hours. But if I add the following
where clause "WHERE StudentHours.FromDate <= ParameterDate" then I
don't get Student XYZ's records anymore. Is there a way I can query so
that I can bring back the Student XYZ row. Like "WHERE
StudentHours.FromDate Is Null or StudentHours.FromDate <=
ParameterDate"?

What's the way to query a null row in the outer join?
 
J

John Vinson

Hi All,

I have an outer join that has a where condition on it. The problem is
that where the subordinate table has no records, the where clause seems
to be changing the query so that it doesn't bring back a row. For
example, if I take the where clause off of the query, I get a row for
Student XYZ even if they have no hours. But if I add the following
where clause "WHERE StudentHours.FromDate <= ParameterDate" then I
don't get Student XYZ's records anymore. Is there a way I can query so
that I can bring back the Student XYZ row. Like "WHERE
StudentHours.FromDate Is Null or StudentHours.FromDate <=
ParameterDate"?

What's the way to query a null row in the outer join?
Click to expand...

Yes; you just posted it. Just open the query in SQL view and add the
IS NULL criterion, or (equivalently) put

IS NULL

on the Criteria row underneath the <= [ParameterDate] criterion.

John W. Vinson[MVP]
 
Top