Facing issue in Access Database query

Question

Order Table Schema

enter image description here

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

enter image description here

Am I missing something ?

Try WHERE DateDiff("d", K.[OrderDate], Date()) > 18 and see if that works any better. — Gord Thompson
– Gord Thompson, Commented Oct 9, 2013 at 12:47

Gord Thompson · Accepted Answer · 2013-10-09 12:58:50Z

2

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

answered Oct 9, 2013 at 12:58

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

asantaballa · Accepted Answer · 2013-10-09 12:49:15Z

1

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?

answered Oct 9, 2013 at 12:49

asantaballa

4,0581 gold badge24 silver badges23 bronze badges

Collectives™ on Stack Overflow

Facing issue in Access Database query

Order Table Schema

My Access Database Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Order Table Schema

My Access Database Query

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related