Criteria: Not In (AllDates)

[Arjay418]

Hi All,

I think this should be simple, maybe you can shed some light.

I have a table of invoices where one of the fields is InvoiceDate. I would like a query that shows me which dates (say from the last 30 days) are not represented in that table.

Example: I have invoices from 01/01/07, 01/02/07, 01/03/07, and 01/05/07. Note: No invoices on 01/04/07 -- so 01/04/07 is not represented in the group. Is there a query that could identify that for me?

One solution that doesn't seem very elegant: Create a table with all dates from 01/01/1990 to 12/31/2050 and call it tblAllDates. Then run a query on my tblInvoices WHERE InvoiceDate Not In (SELECT Date FROM tblAllDates).

Any ideas?

Thanks,

RJ

 

[Golom]

In essence the solution that you came up with is the only SQL way to do it. You can make a minor variation of that and create an Integers table

CREATE TABLE Integers (n As Integer)

INSERT INTO Integers ( n ) VALUES ( 0 )
INSERT INTO Integers ( n ) VALUES ( 1 )
INSERT INTO Integers ( n ) VALUES ( 2 )
  :
INSERT INTO Integers ( n ) VALUES ( 29)
INSERT INTO Integers ( n ) VALUES ( 30)

and then

Select DateAdd("d", -I.N, Date()) As [Missing Date]

From Integers I LEFT JOIN myTable M
     ON DateAdd("d", -I.N, Date()) = M.InvoiceDate

Where M.InvoiceDate IS NULL

Order By 1 ASC