When Filtering Query - Query returns no records when there arer matching records

[D Witherspoon]

I have an MS Access query that is showing some strange behaviour. Whenever
I put a filter on the query no records will show up even if there are
records that match that criteria. I discovered this when I was looking at a
query that was not some reason for pulling all of the records it should have
been. I looked at that query and the query behind the query to try and
discover what the problem was.

Behaviour
=======
For example... If I do the following filter. Where fieldID=1396. No
records show up. When I run the query unfiltered 1396 does indeed show up
in the returned records. So to even furrther ensure that I am filtering
correctly I select the row with the fieldID of 1396 and I select the fieldID
column. Then I click "filter by selection". When I do that... again.. no
records show up.

Has anyone seen a query exhibiting this type of behaviour. This is very
frustrating obviously.

qryWorkLog (this query exhibits the behaviour above)
=====================================
SELECT tblWorkLog.WorkLogID, tblWorkLog.GaugeID, tblWorkLog.WorkDoneID,
qryWorkLog_WithDueDate.LogDate, tblWorkLog.CommentsCorrections,
tblWorkLog.ResultID, tblWorkLog.Initials, tblWorkLog.BeforeActual,
tblWorkLog.AfterGaugeAdjustment, tblWorkLog.StatusID, tblWorkLog.StatusDate,
qryWorkLog_WithDueDate.NextCertDue, qryWorkLog_WithDueDate.DueDate
FROM qryWorkLog_WithDueDate LEFT JOIN tblWorkLog ON
qryWorkLog_WithDueDate.WorkLogID = tblWorkLog.WorkLogID;


qyWorkLog_WithDueDate (this query doesn't seem to exhibit the behaviour
above, but is used in the qryWorkLog query)
==================================================================================
SELECT qry.GaugeID, qry.WorkLogID, qry.LogDate, qry.NextCertDue, qry.DueDate
FROM (SELECT First(wl2.GaugeID) as GaugeID, wl2.WorkLogID,
First(wl2.LogDate) AS LogDate, First(wl2.NextCertDue) AS NextCertDue,
Max(wl1.NextCertDue) AS DueDate
FROM tblWorkLog AS wl1
RIGHT JOIN tblWorkLog AS wl2 ON wl1.GaugeID = wl2.GaugeID
AND wl2.LogDate > wl1.LogDate
AND [wl1].[WorkLogID]<> [wl2].[WorkLogID]
GROUP BY wl2.GaugeID, wl2.WorkLogID
) AS qry
ORDER BY qry.GaugeID, qry.LogDate, qry.NextCertDue, qry.DueDate;

 

[Guest]

While I've never seen that behavior, I did notice that you have
queries built upon other queries plus Left and Righ Joins.
This could be causing confusion that the Lookup tool just can't
handle..

 

[D Witherspoon]

That's what I was thinking..

Maybe it can't handle joins that don't use the equal sign. For example a
Greater than or Less than join.

I am a SQL Server developer. I hate toning my queries down to something MS
Access can actually handle. In the documentation these types of joins are
supported, but MS Access is so goofy so who knows.

I hope someone else has some information on this

While I've never seen that behavior, I did notice that you have
queries built upon other queries plus Left and Righ Joins.
This could be causing confusion that the Lookup tool just can't
handle..
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have an MS Access query that is showing some strange behaviour.
Whenever
I put a filter on the query no records will show up even if there are
records that match that criteria. I discovered this when I was looking
at a
query that was not some reason for pulling all of the records it should
have
been. I looked at that query and the query behind the query to try and
discover what the problem was.

Behaviour
=======
For example... If I do the following filter. Where fieldID=1396. No
records show up. When I run the query unfiltered 1396 does indeed show
up
in the returned records. So to even furrther ensure that I am filtering
correctly I select the row with the fieldID of 1396 and I select the
fieldID
column. Then I click "filter by selection". When I do that... again..
no
records show up.

Has anyone seen a query exhibiting this type of behaviour. This is very
frustrating obviously.

qryWorkLog (this query exhibits the behaviour above)
=====================================
SELECT tblWorkLog.WorkLogID, tblWorkLog.GaugeID, tblWorkLog.WorkDoneID,
qryWorkLog_WithDueDate.LogDate, tblWorkLog.CommentsCorrections,
tblWorkLog.ResultID, tblWorkLog.Initials, tblWorkLog.BeforeActual,
tblWorkLog.AfterGaugeAdjustment, tblWorkLog.StatusID,
tblWorkLog.StatusDate,
qryWorkLog_WithDueDate.NextCertDue, qryWorkLog_WithDueDate.DueDate
FROM qryWorkLog_WithDueDate LEFT JOIN tblWorkLog ON
qryWorkLog_WithDueDate.WorkLogID = tblWorkLog.WorkLogID;


qyWorkLog_WithDueDate (this query doesn't seem to exhibit the behaviour
above, but is used in the qryWorkLog query)
==================================================================================
SELECT qry.GaugeID, qry.WorkLogID, qry.LogDate, qry.NextCertDue,
qry.DueDate
FROM (SELECT First(wl2.GaugeID) as GaugeID, wl2.WorkLogID,
First(wl2.LogDate) AS LogDate, First(wl2.NextCertDue) AS NextCertDue,
Max(wl1.NextCertDue) AS DueDate
FROM tblWorkLog AS wl1
RIGHT JOIN tblWorkLog AS wl2 ON wl1.GaugeID = wl2.GaugeID
AND wl2.LogDate > wl1.LogDate
AND [wl1].[WorkLogID]<> [wl2].[WorkLogID]
GROUP BY wl2.GaugeID, wl2.WorkLogID
) AS qry
ORDER BY qry.GaugeID, qry.LogDate, qry.NextCertDue, qry.DueDate;