Strange results with SQL query against Access-MDB using datediff and HAVING clause RRS feed

  • Question

  • I am running the following SQL against an MDB file, a copy of which is located here: http://hotfile.com/dl/40641614/2353dfc/test.mdb.html (no macros or viruses)

    SELECT datediff("d", MAX(invoice.date), Now) As Date_Diff  
          , MAX(invoice.date) AS max_invoice_date  
          , customer.number AS customer_number  
    FROM invoice   
        INNER JOIN customer   
            ON invoice.customer_number = customer.number  
    GROUP BY customer.number 

    If the following was added:

    HAVING datediff("d", MAX(invoice.date), Now) > 365  

    I would expect it to simply exclude rows with Date_Diff <= 365?

    But I am getting something different, it is excluding more than just this. What is going wrong?

    Saturday, May 1, 2010 5:31 AM

All replies

  • Without seeing any data results, could it be the INNER JOIN that is eliminating some of the results that you are expecting to be returned by the query?

    You might want to break down your query to see how the different expressions are impacting the results.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Saturday, May 1, 2010 2:01 PM