Identify new data based on ID and Date-CodePudding

The following SQL query does not detect change in my Table :

SELECT ID FROM Test WHERE TheDate=#21/10/2021#  AND NOT EXISTS
(SELECT ID FROM Test WHERE TheDate=DateAdd("d", -1, #21/10/2021#))

For example with this table :

TheDate     ID
21/10/2021  1
21/10/2021  7
20/10/2021  1
20/10/2021  2
20/10/2021  3
19/10/2021  3
18/10/2021  3

The Above Query should return 7.

My goal is to retrieve New ID that does not exist 1 Day before.

What am I doing wrong ?

CodePudding user response：

There exists records from that date. Try using IN:

SELECT ID From Test WHERE TheDate=#21/10/2021# AND ID NOT IN
(SELECT ID FROM Test WHERE TheDate=DateAdd("d", -1, #21/10/2021#))

Or:

SELECT ID From Test WHERE TheDate=#2021/10/21# AND ID NOT IN
(SELECT ID FROM Test WHERE TheDate=DateAdd("d", -1, #2021/10/21#))