MS Access: How to create a report that how many dates are missing in the database

Question

I have a dataset in MS Access and I enter data through a form. I am entering the data date wise but I want to create a report which shows that how many dates are missing in the database from a specific date. Help me to create it.

Answer 1

You could create three tables (each table has one field):

1. [Months] will have 12 records, one for each MonthNumber.
2. [Days] will have 31 records, one for each DayNumber.
3. [Years] will have a record for each possible YearNumber.

Then create a query (call it AllDates) to generate all possible dates from those three tables:

SELECT DateSerial([YearNumber],[MonthNumber],[DayNumber]) AS RangeDate
FROM [Days], [Months], [Years]
WHERE (((IsDate([MonthNumber] & "/" & [DayNumber] & "/" & [YearNumber]))=True));

Then, your report query could be:

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT AllDates.*
FROM AllDates LEFT JOIN FormDates ON AllDates.RangeDate = FormDates.DateEntered
WHERE (((FormDates.DateEntered) Is Null) AND ((AllDates.RangeDate)>=[StartDate] And (AllDates.RangeDate)<=[EndDate]));

FYI: The [FormDates] table is the table you already have (i.e. entered from your form)