Access wiki - Access Help and How-to - Microsoft Office > Viewing Find Missing Dates or Numbers within a Range
|
|
Menu
Content
Resources
To Do
Toolbox
|
←Older revision | Newer revision→ This article was originally created by Dane Miller, aka dallr [1] and the sample application with the code can be found here: Media:Find Missing Dates or Numbers within a Range.zip It shows how to use SQL to identifying missing dates or numbers within a range. Identifying Missing Numbers The table structure: Table Name : Tbl_Numbers Field name : InvoiceNo So if your data is like this. 100 101 106 107 109 115
102-105 108 110-114
To use the code all you need to adjust is the inner query in the FROM Clause with the subquery. Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query. CODE SELECT T3.YourNumber , T3.NextNum , IIf([NextNum]-[YourNumber]=2,[YourNumber]+1,[YourNumber]+1 & "-" & [NextNum]-1) AS MissingNumbers FROM (SELECT Tbl_Numbers.InvoiceNo AS YourNumber ,(SELECT MIN(InvoiceNo) FROM Tbl_Numbers T1 WHERE T1.InvoiceNo >Tbl_Numbers.InvoiceNo) AS NextNum FROM Tbl_Numbers) AS T3 WHERE ((([NextNum]-[YourNumber])>1));
Identifying Missing Dates So if your data is like this. 8/1/09 8/2/09 8/5/09 8/10/09 8/14/09
8/3/09 - 8/4/09 8/6/09 - 8/9/09 8/11/09 - 8/13/09 Date Example 1: This version is not in the sample db but it is easier to understand. To use the code all you need to adjust is the inner query in the FROM Clause with the subquery. Note: If you have extremely large amounts of records then it might be best to use the inner query and make a table then use that table as the source for the outer query. CODE SELECT D3.YourDate , D3.NextDate , IIf(DateDiff("d",[YourDate],[NextDate])=2,DateAdd("d",1,[YourDate]),DateAdd("d",1,[YourDate]) & " - " & DateAdd("d",-1,[NextDate])) AS MissingDates FROM (SELECT tbl_Dates.TheDate As YourDate , (SELECT Min(TheDate) FROM Tbl_Dates D1 WHERE D1.TheDate >Tbl_Dates.TheDate) As NextDate FROM tbl_Dates) AS D3 WHERE (((DateDiff("d",[YourDate],[NextDate]))>1));
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;
--Dallr 08:47, 24 July 2010 (EDT)
|
Find Missing Dates or Numbers within a Range
 |
| Disclaimers |