find "x days in a row" from query results

[grades]

Hi all,
I have a query that returns various dates, in my case it's the dates that an employee was absent.
What I need to do is take the highest date, and see if any days in a row are present in the query.

Lets say the query returns this:
2/5/2012
2/4/2012
1/1/2011

I'd want it to be able to realize that there are 2 days in a row, counting back from the most recent.

I'm not sure even how to really start on this, maxofAbsentDate in my report?


Hopefully I am being clear.
Thanks,

 

[AccessMSSQL]

You probably will need to use some VBA for this. Do you only want to know if the last date absent had multiple days in a row or do you want to find out how many times overall an employee was absent more than 1 day in a row?

If you want to only find the latest absent date you can use a VBA function in your report. This would be a function to use if your tables are stored in Access.

Public Function MaxAbsentDays(LastAbsentDate As Date, EmployeeID As Integer)
Dim rs As dao.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * FROM EmployeeTimeCard WHERE EmployeeID = " & EmployeeID
strSQL = strSQL & " WHERE Absent = True"
strSQL = strSQL & " AND AbsentDate < #" & LastAbsentDate & "#"
strSQL = strSQL & " ORDER BY AbsentDate DESC"
i = 0
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbOpenDynamic)
If Not rs.EOF Then
    Do Until rs.EOF
        If rs("AbsentDate") = DateAdd(d, -1, LastAbsentDate) Then
            i = i + 1
        Else
                Exit Do
        End If
        rs.MoveNext
    Loop
End If
    rs.Close
    Set rs = Nothing
MaxAbsentDays = i + 1
End Function

You will also need to add a reference to your project: Microsoft Access Active X Data objects

 

[grades]

Very nice, this is what i ended up with:

Dim db As dao.Database, rs As dao.Recordset, qdf As dao.QueryDef
Dim strSQL As String

Dim i As Integer
Set db = CurrentDb()
 Set qdf = db.QueryDefs("6-abandonment")
qdf.Parameters(0) = Forms!frmlookup!AName.Value

i = 0

Set rs = qdf.OpenRecordset

If Not rs.EOF Then
   Do Until rs.EOF
       If rs("dateoccur") = DateAdd("d", -i, Me.Text20) Then
           i = i + 1
       Else
               Exit Do
       End If
       rs.MoveNext
   Loop
End If
   rs.Close
   Set rs = Nothing
maxabsentdays = i

+1 from me

 

[AccessMSSQL]

great! glad that worked for you.