Query to find Date Gaps (1 Viewer)

[soldat452002]

Hello ,

I have some data that needs has two dates Date Effective and Date Expiration with a Producer ID (PK). I need a query to search the Producer ID and find date gaps between these two Date Fields (date Eff), (Date Exp).

Can someone guide me how to do this in a query? I have like 90,000 Records. Thanks

ProducerId Year DateEff DateExp
0000300063 2007 1/1/2007 12/31/2007
0000300063 2008 1/1/2008 12/31/2008
0000300063 2009 1/1/2009 12/31/2009
0000300063 2010 1/1/2010 12/31/2010
0000300063 2011 1/1/2011 12/31/2011
0000300063 2012 1/1/2012 12/31/2012
0000300063 2013 1/1/2013 12/31/2013
0000300063 2014 1/1/2014 12/31/2014
0000300063 2015 9/22/2015 12/31/2015
0000300063 2016 1/1/2016 1/1/2200

 

[KenHigg]

Seems like you could just subtract date 1 from date 2 and if the difference was greater than 1 - bingo...

 

[soldat452002]

Yeah I tried that, but with 90,000 records with different Producer IDs is difficult to pinpoint which records have gaps. For example , if the Producer ID is not the same, it would calculate a Gap between the new and previous row.

 

[KenHigg]

Not sure I understand. You mean from one record to the next?

 

[arnelgp]

what do you need then. only show which records has gap date?

 

[arnelgp]

create a function in a module. then call it in your query. now you only need to filter the resulting recordset for dategap greater than 1:

public function fnGap(pID as String, pYear As Long, pExpire As Date) As Integer
dim rs As Dao.RecordSet
Set rs = Dbengine(0)(0).OpenRecordset("Select Top 1 DateEff From yourTable " & _
"Where ProducerID = '" & pID & "' And [Year] > " & pYear & " Order By [Year];", _
dbOpenSnapshot)
With rs
If Not (.Eof And .BOF) then
.MoveFirst
fnGap = DateDiff("d", pExire, !DateEff)
Else
fnGap=1
End If
.Close
End With
set rs=nothing
End Function

Now On your query:

Select ProducerID, [Year], DateEff, DateExpr, fnGap([ProducerID],[Year],[DateExpr]) As DaysGap From yourTABLE;

 

[soldat452002]

Hello, Thanks for Module, I'm getting an error "Run-Time error '3464': Data Type mismatch in criteria expression.

Module:

Public Function fnGap(pID As String, pYear As Long, pDateExp As Date) As Integer
Dim rs As Dao.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("Select Top 1 DateEff From Lookup " & _
"Where ProducerID = '" & pID & "' And [Year] > " & pYear & " Order By [Year];", _
dbOpenSnapshot)
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
fnGap = DateDiff("d", pDateExp, !DateEff)
Else
fnGap = 1
End If
.Close
End With
Set rs = Nothing
End Function


SELECT Lookup.ProducerID, Lookup.Year, Lookup.DateEff, Lookup.DateExp, fnGap([ProducerID],[Year],[DateExp]) AS DaysGap
FROM Lookup;

 

[arnelgp]

please specify the fieldtype on your table:

ProducerId = Is it Text or Numeric?
Year = Is it text or numeric
DateEff = is it date type?
DateExp = is it date type?

 

[soldat452002]

Hi, all are Text, Except the Dates fields (DateEff, DateExp)

Year = Text
ProducerID = Text
DateEff = Date
DateExp = Date

 

[arnelgp]

have to modify the code, here it is:

Public Function fnGap(pID As String, pYear As String, pDateExp As Date) As Integer
Dim rs As Dao.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("Select Top 1 DateEff From Lookup " & _
"Where ProducerID = '" & pID & "' And [Year] > '" & pYear & "' Order By [Year];", _
dbOpenSnapshot)
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
fnGap = DateDiff("d", pDateExp, !DateEff)
Else
fnGap = 1
End If
.Close
End With
Set rs = Nothing
End Function

 

[soldat452002]

Thank you! It works like a charm!