Facing issue in Access Database query

Question

Order Table Schema

My Access Database Query

Select * from 
(
     SELECT reseller.id, Max(orders.[order date]) as OrderDate
     FROM Reseller INNER JOIN orders ON Reseller.ID = orders.ResellerID
     group by reseller.id
)K
WHERE (((K.[OrderDate]) Not Between (Date()-1) And (Date()-18)))

To find those reseller that did not order for 18 Days.

But this is giving below records

Am I missing something ?

Answer 1

Try this query instead:

SELECT * 
FROM 
    (
        SELECT reseller.id, Max(orders.[order date]) AS OrderDate
        FROM 
            Reseller 
            INNER JOIN 
            orders 
                ON Reseller.ID = orders.ResellerID
        GROUP BY reseller.id
    ) K
WHERE DateDiff("d", K.[OrderDate], Date()) > 18

Answer 2

Try this

Select * from 
(
     SELECT reseller.id, Max(orders.[order date]) as OrderDate
     FROM Reseller INNER JOIN orders ON Reseller.ID = orders.ResellerID
     group by reseller.id
)
WHERE (((K.[OrderDate]) Not Between (    DateAdd("d", -1, Date()) 
                                     And DateAdd("d", -18, Date())
                                    )

I think when you do Date() - 1 it is subtracting 1 time internal until like subpart of second from the date, not what you intended to subtract 1 date

ALSO, did not change, but do you really want to make the top limits today - 1 day, or just today?