I have an extract of data in an access database which essentially represents an access control system, and one of the fields if formatted in date/time, i.e. 03/06/2019 08:27:02 - these represent the times an employee swiped into and out of one of our buildings. What I need is a way of getting a unique list of days where there are no entries whatsoever in the data at all in between a date range of 01/06/2019-31/07/2019 only. So for example if there were entries in the date for 04/06/2019 then that wont be returned in the results as there are entries on that day, but if there are no entries at all for 05/06/2019 then I need that to be returned in my results along with any other unique dates that have no records whatsoever in the table. Can you think of a way of achieving this with a query? I don't want anymore though than the 'missing days' between the range specified if at all possible.

n/b at present its in an access table but we could just as easy export to excel and use that if it will be easier to get the desired result.

n/b at present its in an access table but we could just as easy export to excel and use that if it will be easier to get the desired result.

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

```
SELECT DISTINCT Abs([id] Mod 10) AS N
FROM MSysObjects;
```

Save this as Then create this query:

```
PARAMETERS
[DateStart] DateTime,
[DateEnd] DateTime;
SELECT
[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000 AS Id,
[DateStart] AS DateStart,
[DateEnd] AS DateEnd, DateAdd("d",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000,[DateStart]) AS [Date]
FROM
Ten AS Ten_0,
Ten AS Ten_1,
Ten AS Ten_2,
Ten AS Ten_3,
Ten AS Ten_4,
Ten AS Ten_5,
Ten AS Ten_6,
Ten AS Ten_7
WHERE (((DateAdd("d",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000,[DateStart])) <= [DateEnd])
AND ((Ten_0.N)<=DateDiff("d",[DateStart],[DateEnd])\1)
AND ((Ten_1.N)<=DateDiff("d",[DateStart],[DateEnd])\10)
AND ((Ten_2.N)<=DateDiff("d",[DateStart],[DateEnd])\100)
AND ((Ten_3.N)<=DateDiff("d",[DateStart],[DateEnd])\1000)
AND ((Ten_4.N)<=DateDiff("d",[DateStart],[DateEnd])\10000)
AND ((Ten_5.N)<=DateDiff("d",[DateStart],[DateEnd])\100000)
AND ((Ten_6.N)<=DateDiff("d",[DateStart],[DateEnd])\1000000)
AND ((Ten_7.N)<=DateDiff("d",[DateStart],[DateEnd])\10000000));
```

Save it as Next, extract the meeting dates:

```
Select Distinct DateValue([Timestamp]) As [Date] From YourTimestampTable
```

Save this as

Finally, create a query using these queries:

```
SELECT
DatesRange.Date
FROM
DatesRange
LEFT JOIN
MeetingDates
ON DatesRange.Date = MeetingDates.Date
WHERE
MeetingDates.Date Is Null;
```

This will list your dates with no meetings.
I think I would start out with:

```
PARAMETERS [CheckStart] DateTime, [CheckEnd] DateTime;
SELECT DISTINCT DateAdd("d", lng_Number, DateValue([yourDateField])) as DateRange
FROM yourTable, tbl_Numbers
WHERE DateAdd("d", lng_Number, DateValue([yourDateField])) >= [CheckStart]
AND DateAdd("d", lng_Number, DateValue([yourDateField])) <= [CheckEnd]
```

This would get you a list of the days between the start and end dates of your range. Then, you could use that as a subquery to identify the missing dates.

```
PARAMETERS [CheckStart] DateTime, [CheckEnd] DateTime;
SELECT DR.DateRange as MissingDates
FROM (
SELECT DISTINCT DateAdd("d", lng_Number, DateValue([yourDateField])) as DateRange
FROM yourTable, tbl_Numbers
WHERE DateAdd("d", lng_Number, DateValue([yourDateField])) >= [CheckStart]
AND DateAdd("d", lng_Number, DateValue([yourDateField])) <= [CheckEnd]
) as DR
LEFT JOIN yourTable on DR.DateRange = DateValue([yourDateField])
WHERE yourTable.ID IS NULL
```

Of course you will need to change "yourTable" to the name of your table, "yourDateField" to the name of your date/time field, and "ID" to the name of any field in your table which will always contain data.
## Premium Content

You need an Expert Office subscription to comment.Start Today