Automate finding missing dates

[TotheRiver]

I want to automate the ability to find the missing dates in a set of data.
Currently, I can run a query that finds the min and max date of the data set. From those min and max dates, I type that into a VBA code that populates a table with all the dates between those min and max dates. Then I compare my new table with my data set. If I could somehow get the min and max dates to show up in my VBA code, that would solve the problem, but so far I have not found a way to do that.
Maybe there is another route as well.

 

[jebry]

Hi!

Try this:

Dim dtMin As Date
Dim dtMax As Date
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = "Select Max(YourDateField) as MaxDate, Min(YourDateField) As MinDate From YourTable"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

dtMin = rst!MinDate
dtMax = rst!MaxDate

Now dtMin and dtMax will hold the information you are looking for.

hth
Jeff Bridgham
bridgham@purdue.edu