Select Records that Start at a Break in the Date

[58djoker via AccessMonster.com]

I am looking for a function for a query that will select all records from
date() back to the first day there was no record entered for that day. So if
I have records for dates 1-10 Nov no records for 11-12 and then records for
13-30, I want it to only find the consecutive records 13-30 assuming today is
30 Nov. Any help?

Thanks Rob

 

[Michel Walsh]

If the dateTime field has only a date (ie, at midnight, or at 00:00:00 if
you prefer, for the time), and if there is only ONE record per date, then:


SELECT *
FROM tableName AS a
WHERE (1+ date() - a.dateTime) = (SELECT COUNT(*)
FROM
tableName AS b
WHERE
b.dateTime >= a.dateTime )



If you have more than one record per date, make a distinct query, over the
date, in a saved query, and use that saved query instead of the table, in
the previous query (to return the dates).



Hoping it may help,
Vanderghast, Access MVP

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers 0
(zero) through your maximum spread (I used 100).
The first query give list of dates from today backwards for 100 days.
58djoker_1 ---
SELECT TOP 100 DateAdd("d",-[CountNUM],Date()) AS DateList
FROM CountNumber;

The second query finds the first descinding missing date in your table.
58djoker_2 ----
SELECT TOP 1 [58djoker_1].DateList
FROM 58djoker_1 LEFT JOIN 58djoker ON [58djoker_1].DateList =
[58djoker].ActionDate
WHERE ((([58djoker].ActionDate) Is Null))
ORDER BY [58djoker_1].DateList DESC;

The third query pulls all records with dates greater than the missing date.
58djoker_3 ---
SELECT [58djoker].*
FROM 58djoker, 58djoker_2
WHERE ((([58djoker].ActionDate)>[DateList]))
ORDER BY [58djoker].ActionDate DESC;