Solved find missing date gaps (1 Viewer)

murray83

Games Collector
Local time
Today, 02:50
Joined
Mar 31, 2017
Messages
646
so have a front end which is just used to find and open a PDF file, bit quicker and better looking then searching through file upon file.

but have come upon a question that I'm sure can be answered by you smart lot, have searched here but didn't find anything on the subject.

but what i would like is a query which looks at the main data, selects the distinct Dt ( my date field name as cant use Date and sounds similar) and then display missing dates.

so far have just done a little SQL which has as hoped pulled the Dt out

Code:
SELECT DISTINCT Main.Dt
FROM Main
ORDER BY Main.Dt ASC ;

but how would i make it look for missing dates, am i going to need a table with each day for the whole year ??
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:50
Joined
Oct 29, 2018
Messages
13,706
Hi. You don't need a table of dates, but you could use a table of sequential numbers.
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:50
Joined
Mar 14, 2017
Messages
3,216
am i going to need a table with each day for the whole year ??
This is the most straightforward approach I could think of right off hand, but you could programmatically create this table with minimal effort

Code:
Sub PopulatetblDates()
'assumes a table called tblDates with one column called dtDate
Dim dtStart As Date, dtLoop As Date, strSQL As String
dtLoop = CDate("1/1/2020")

Do Until dtLoop > CDate("12/31/2020")
    strSQL = "insert into tblDates(dtDate) values(#" & dtLoop & "#)"
    CurrentDb.Execute strSQL, dbFailOnError
    dtLoop = dtLoop + 1
Loop

End Sub

Then do a Find Unmatched query wizard joining on your date fields. (depending on the type of date format you are storing in your table - this approach may need some adjustment).
 
Last edited:

murray83

Games Collector
Local time
Today, 02:50
Joined
Mar 31, 2017
Messages
646
Hi. You don't need a table of dates, but you could use a table of sequential numbers.

OK, but again how would that work ? sorry if im being daft

see current DB, but the pdfs wont open sorry as on shared drive here at work
 

Attachments

  • Daventry Digital Paperwork Archive.accdb
    556 KB · Views: 13

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:50
Joined
Oct 29, 2018
Messages
13,706
OK, but again how would that work ? sorry if im being daft

see current DB, but the pdfs wont open sorry as on shared drive here at work
Hi. Sorry, I'm only on my phone right now, so I can't take a look at your file yet. But, the article should show you how you can generate sequential dates without having to create a dates table.
 

murray83

Games Collector
Local time
Today, 02:50
Joined
Mar 31, 2017
Messages
646
Hi. Sorry, I'm only on my phone right now, so I can't take a look at your file yet. But, the article should show you how you can generate sequential dates without having to create a dates table.

ok but like you will have to read it on my phone on break as looks like blocked on works PC, boo
 

Attachments

  • not at work.png
    not at work.png
    40.4 KB · Views: 17

Isaac

Lifelong Learner
Local time
Yesterday, 18:50
Joined
Mar 14, 2017
Messages
3,216
I was actually curious about dbGuy's method too, will be interesting to see how it works. Unfortunately my work PC also blocks your website dbGuy :(
Phooey on corporate IT blocks! :)

PS @murray83 I've edited my original post in case it helps.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:50
Joined
Oct 29, 2018
Messages
13,706
ok but like you will have to read it on my phone on break as looks like blocked on works PC, boo
Okay, thanks for letting me know. I'll take a look at your file as soon as I get back in front of a computer.
 

Micron

AWF VIP
Local time
Yesterday, 21:50
Joined
Oct 20, 2018
Messages
3,471
Unfortunately my work PC also blocks your website dbGuy
Such a policy is common - keeps you from looking at porn or booking your vacation on company time! However, if I could justify a certain url as being work related, they would create an exception. Maybe your company has such a policy too?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:50
Joined
Oct 29, 2018
Messages
13,706
I was actually curious about dbGuy's method too, will be interesting to see how it works. Unfortunately my work PC also blocks your website dbGuy :(
Phooey on corporate IT blocks! :)

PS @murray83 I've edited my original post in case it helps.
That is unfortunate. :(
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:50
Joined
Mar 14, 2017
Messages
3,216
Such a policy is common - keeps you from looking at porn or booking your vacation on company time! However, if I could justify a certain url as being work related, they would create an exception. Maybe your company has such a policy too?
Yes, common indeed. In previous jobs, the Block page had a very handy interface for requesting exceptions. Here it is more involved a process, but considering the value of dbGuy's site I just may initiate same! :geek:
 

Micron

AWF VIP
Local time
Yesterday, 21:50
Joined
Oct 20, 2018
Messages
3,471
...especially if there is a utility there that you need. Hopefully they would not impose an expiry date on your access to it.
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:50
Joined
Mar 14, 2017
Messages
3,216
Thanks, I was able to get to that link.
Hmm, that post still references the need to create and populate a table prior to creating the query. If actions like that are still required in this scenario, then it doesn't seem any easier than what I suggested - populate a table with all dates (takes about 2 seconds to run), then do a query from there with a left join to the main table where the main table.Datefield is null (aka a find unmatched query).
 

namliam

The Mailman - AWF VIP
Local time
Today, 03:50
Joined
Aug 11, 2003
Messages
11,691
SELECT DISTINCT Main.Dt, Main.dt + 1 Missing_Dt
FROM Main
left join Main Main2 on main.dt + 1 = main2.dt
where main2.dt is null
ORDER BY Main.Dt ASC ;

only issue here can be 2 missing dates in a row or multiple
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:50
Joined
Oct 29, 2018
Messages
13,706
OK, but again how would that work ? sorry if im being daft

see current DB, but the pdfs wont open sorry as on shared drive here at work
Hi. I downloaded your file; unfortunately, you didn't provide any data by including the BE file as well or converting the linked tables into local tables, so I can't really tell what you want to do with them.
 

murray83

Games Collector
Local time
Today, 02:50
Joined
Mar 31, 2017
Messages
646
Hi. I downloaded your file; unfortunately, you didn't provide any data by including the BE file as well or converting the linked tables into local tables, so I can't really tell what you want to do with them.

oopss let me re attach the tables, sorry

query working on is Query 1
 

Attachments

  • Daventry Digital Paperwork Archive - Copy.accdb
    1.3 MB · Views: 12

murray83

Games Collector
Local time
Today, 02:50
Joined
Mar 31, 2017
Messages
646
Code:
SELECT DISTINCT Main.Dt, DtChecker.Dt
FROM Main RIGHT JOIN DtChecker ON Main.Dt = DtChecker.Dt
WHERE Main.Dt Is Null
ORDER BY Main.Dt;

works and shows me the missing dates as seen here
 

Attachments

  • missing.png
    missing.png
    16.8 KB · Views: 16

Users who are viewing this thread

Top Bottom