Access wiki - Access Help and How-to - Microsoft Office > Viewing Find Missing Dates or Numbers within a Range
|
|
Menu
Content
Resources
To Do
Toolbox
|
Revision as of 12:04, 26 July 2010This 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: 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 |