Results 1 to 3 of 3

Thread: Quiery Bug

  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    28

    Question Unanswered: Quiery/Access Bug ?!?

    Hi All

    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

    WHERE
    1. (((QRY_ACCESS_RESYNCS.SHIPPER_DATE_ON)<=[QRY_RESYNC_DATES]![RSEnd]) AND ((QRY_ACCESS_RESYNCS.SHIPPER_DATE_OFF)>=[QRY_RESYNC_DATES]![RSEnd]
    Or
    2. (QRY_ACCESS_RESYNCS.SHIPPER_DATE_OFF) Is Null)
    AND
    3. ((QRY_ACCESS_RESYNCS.REC)="P"))
    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

    Thanks in advance..
    Attached Files Attached Files
    Last edited by Mara; 05-21-03 at 05:19.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, a wild guess then:
    Code:
    WHERE 
            QRY_ACCESS_RESYNCS.SHIPPER_DATE_ON
          <=[QRY_RESYNC_DATES]![RSEnd]        
       AND (
            QRY_ACCESS_RESYNCS.SHIPPER_DATE_OFF
          >=[QRY_RESYNC_DATES]![RSEnd] 
         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

    rudy
    http://r937.com/

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    28
    Your wild guess was spot on...

    Many Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •