The problem or possible bug was found in a quiery, the quiery itself is quite large and consists of several nested levels of quieries. The problem that was being looked into was that it returned no results when run for particular data: a data problem then?
The quirey was broken down and run in parts
1. (((QRY_ACCESS_RESYNCS.SHIPPER_DATE_ON)<=[QRY_RESYNC_DATES]![RSEnd]) AND ((QRY_ACCESS_RESYNCS.SHIPPER_DATE_OFF)>=[QRY_RESYNC_DATES]![RSEnd]
2. (QRY_ACCESS_RESYNCS.SHIPPER_DATE_OFF) Is Null)
ORDER BY TBL_REC_MPR_REAPPORT.MPR, TBL_REC_MPR_REAPPORT.RESYNC_ID, TBL_REC_MPR_REAPPORT.GFD;
It should return results as when the parts are run all the relevant results are there, when run as a whole nothing is returned!?!
Removing the condition ="P" solved this problem and returned a aprox 200 records containing "P" which could then be filltered for!?!
The condition P was then removed and placed in a new quiery which was based on the origional one. It was found that this did return results MOST OF THE TIME?!!!?
Every so often when the quiery was run it would without warning return nothing in complete disregard to what it had done the time before or after
Does anyone know what is going on here, come across this before or like to make a wild guess as this problem needs to be solved
Or QRY_ACCESS_RESYNCS.SHIPPER_DATE_OFF Is Null
see the two conditions inside the AND block? what if the column value is null? the database will then evaluate NULL >= [RSEnd] which of course evaluates as NULL, thus when NULL is ANDed the result is NULL, so you get nothing!
try switching the sequence of the two parts of the OR so that the null test comes first