This article was orginally created by Dane Miller, aka dallr.

It shows how to use SQL to identifying missing dates or numbers within a range.

An example of where this can be used is if you have a range of dates and each day must be populated with a record. This can cause you to easily identify any missing date records.

Date Example Code:

SELECT DATEADD("d",1,a.theDate) AS GapStart, DATEADD("d",-1,b.theDate) AS GapEnd FROM

    ( SELECT tbl_Dates.TheDate, (SELECT COUNT(*) FROM Tbl_Dates D1 WHERE D1.TheDate <= tbl_Dates.TheDate) AS ID
      FROM tbl_Dates ) AS a 
  INNER JOIN 
    ( SELECT tbl_Dates.TheDate, (SELECT COUNT(*) FROM Tbl_Dates D1 WHERE D1.TheDate <= tbl_Dates.TheDate) AS ID
     FROM tbl_Dates ) AS b 
  ON a.ID=b.ID-1

WHERE DATEDIFF("d",a.theDate,DATEADD("d",-1,b.theDate))<>0;

Number Example Code:

SELECT T3.InvoiceNo , T3.NextNum , IIf([NextNum]-[InvoiceNo]=2,[Invoiceno]+1,[InvoiceNo]+1 & "-" & [NextNum]-1) AS MissingNumbers

FROM (SELECT Tbl_Numbers.InvoiceNo,

             (SELECT MIN(InvoiceNo) FROM Tbl_Numbers T1 WHERE T1.InvoiceNo >Tbl_Numbers.InvoiceNo  ) AS NextNum
       FROM Tbl_Numbers) AS T3

WHERE ((([NextNum]-[InvoiceNo])>1));

--Dallr 08:47, 24 July 2010 (EDT)