Missind dates in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - I have a table with a field that stores date value.

What do i do if i want to get a list of the dates from January1st to today
that are not in this date field.

Thanks a lot!!
 
Easy method is to build a simple table that has a date field and have all
the dates in it for the year or have a utility table with integers in it
from 1 to 366.

SELECT TblDates.TheDate
FROM TblDates LEFT JOIN YourTable
On tblDates.TheDate = YourTable.TheDateField
WHERE YourTable.TheDateField is Null

With an integer table that has an integer field you could try something
like:

SELECT DateSerial(Year(Date()),1,tblIntegers.TheNumber)
FROM tblIntegers LEFT JOIN YourTable
ON DateSerial(Year(Date()),1,tblIntegers.TheNumber) = YourTable.TheDateField
WHERE YourTable.TheDateField is Null and
tblIntegers.TheNumber Between 1 and
DatePart("y",DateSerial(Year(Date()),12,31))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Hi - I have a table with a field that stores date value.

What do i do if i want to get a list of the dates from January1st to today
that are not in this date field.

Thanks a lot!!
Click to expand...

First create a new table.

Field Name: MissDate DateTime, Indexed No Duplicates
Name the table tblMissingDates

Next copy and paste the below Sub into a new module to fill the new
table with all the dates from 1/1/2007 to the current date.


Public Sub MissingDates()
Dim Db As DAO.Database
Set Db = CurrentDb
Dim rs As DAO.Recordset
Set rs = Db.OpenRecordset("tblMissingDates")
Dim dteDate As Date
dteDate = #1/1/2007#

Do While dteDate <= Date
With rs
.AddNew
!missdate = dteDate
.Update
dteDate = dteDate + 1
End With
Loop

Set rs = Nothing
Set Db = Nothing

End Sub

Run this sub to fill the table with all of the dates.


Next run the Find Unmatched Query Wizard.
Follow it's instructions.

Below is my sample query to find unmatched records.
Change the field and table names as needed.

SELECT tblMissingDates.*
FROM tblMissingDates LEFT JOIN tblMyTable ON tblMissingDates.MissDate
= tblMyTable.DateField
WHERE (((tblMyTable.DateField) Is Null));

The result will be a query list of all missing dates in tblMyTable.

Note: Make sure all the dates entered in your current table are just
the date, without any time value.
 
Back
Top