Find any missing dates

[ry94080]

Hello all,

I have an automated report that runs every day and logs to a table that it has run for that day.

Every once in awhile, the report will fail and not run on a particular day.

The table will be missing a date as seen below. As you can see, 1/3/2024 is missing. How can i write a query that would look for missing dates? So, that i can have my application rerun for that particular date?


1/1/2024
1/2/2024
1/4/2024
1/5/2024



Any help appreciated.

[Welshgasman]

All I can think of is comparing against a full table of dates, with the Not Matching query.

[ry94080]

Thanks for the reply. That crossed my mind too, but i was trying to avoid having to maintain a table full of dates.

May have to go with that, if there aren't any other ideas.

Thanks again!

[pbaldy]

You could loop the records and compare each to the one before, or loop dates and check if each is in the table, but the suggested table is probably simplest.

[Welshgasman]

Thanks for the reply. That crossed my mind too, but i was trying to avoid having to maintain a table full of dates.

May have to go with that, if there aren't any other ideas.

Thanks again!

Not hard to do.
Create a Table for Day,Month and Year and one for Dates.

Then run this query.

INSERT INTO tblDates ( StepsDate )
SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates
FROM tblDay, tblMonth, tblYear
WHERE (((DateSerial([YearNo],[MonthNo],[DayNo])) Between [Tempvars]![DtFrom] And [Tempvars]![dtTo]) And ((IsDate([MonthNo] & "/" & [DayNo] & "/" & [YearNo]))<>False))
ORDER BY DateSerial([YearNo],[MonthNo],[DayNo]);

How you determine the range is up to you. I used a form to populate the TempVars.

[NoellaG]

maybe create 2 work tables with an autonumber field + date field.
First action: insert the dates in table 1, sorted ascending by date.
Second action: duplicate this table with a make table query, make sure the number field there is a long integer
Third action: update the number col of table 2: number = number - 1
Then create a query where you join both tables on the number field.
You should have something like

Numberfield date 1 date 2
1 1/1/2024 1/2/2024
2 1/2/2024 1/4/2024
3 1/4/2024 1/5/2024
4 1/5/2024

then query for records where col2 - col1 >= 2

[madpiet]

you could create a tally or numbers table that goes from 0 to <pick a number> and then use DATEADD() with it to create a table of dates. Then you basically do the outer join trick. Zzzzz.

Shame you can't just use something like LAG() or LEAD() to look at the previous or next records. Then you could just use DATEDIFF with those and if the answer isn't 1, then you have a gap.

[June7]

Emulating LAG() or LEAD() in Access requires correlated subquery. Be aware this can perform slowly with large dataset.

Consider:
SELECT [ReportDate]-1 AS MissingDte
FROM Reports
WHERE ((([ReportDate]-(SELECT TOP 1 ReportDate FROM Reports AS Dupe WHERE Dupe.ReportDate < Reports.ReportDate ORDER BY Dupe.ReportDate DESC))>1));

However, this assumes there would not be multi-date gaps.

It is possible for query to dynamically generate a dataset of all dates within a given range, but again, can perform slowly.

Why would a report fail to run? Could there ever be multiple reports on same date? What else is done with this report - exported, saved as PDF?

[CJ_London]

an alternative method is to use a non standard join

assumptions:

the field is a date field
the field does not contain a time element (perhaps hidden by formatting)
there are no contiguous missing dates (although the query can be run multiple times)

Using June's table/field names

SELECT A.ReportDate+1 AS MissingDate
FROM Reports A LEFT JOIN Reports B ON A.ReportDate+1=B.ReportDate
WHERE B.ReportDate is Null AND A.ReportDate<>dmax('ReportDate','Reports')

You need the dmax comparision to exclude the last record which will never have a following record

vba code might be something like this to loop through the code multiple times

dim sqlStr as string
dim rs as dao.recordset
dim hasUpdates as boolean

sqlstr="SELECT A.ReportDate+1 AS MissingDate" & _
" FROM Reports A LEFT JOIN Reports B ON A.ReportDate+1=B.ReportDate" & _
" WHERE B.ReportDate is Null l AND A.ReportDate<>dmax('ReportDate','Reports')"

hasUpdates=true

while hasUpdates
    set rs=currentdb.openrecordset sqlstr
    rs.movefirst
    hasupdates=not rs.eof
    while not rs.eof
       runreport rs!missingdate 'whatever you are using to run the report
       rs.movenext
    wend 'rs.eof
wend 'hasupdates

[June7]

Right, I keep forgetting about non-standard join.

Will not be able to build or even view in query design view, only SQL view.

[moke123]

I was fiddling around and came up with a different approach, not a query, but I thought I'd throw it out there.
I populate a dictionary of the dates for a given month. I then loop though the table of dates and use the dictionary exists method and if a date exists in the table I remove it from the dictionary.
Whatever is left over in the dictionary at the end is missing dates which are written to a listbox.
There's alot of code in the demo which is only for the demo. The top 2 subs are the relevant ones. The findmissing sub can be called in many places.

[CJ_London]

Will not be able to build or even view in query design view, only SQL view.

agreed, but the same goes for other queries such as union queries. And does have the benefit of being able to use indexing (assuming OP has set them)

EDIT: However you can build the basic query - joining on ON A.ReportDate=B.ReportDate, then move to sql view to add the +1 to A.ReportDate

[ry94080]

THANKS EVERYONE! Will have to go through and weigh all of these options