Need simple counter module

[smpayne]

I have a table in my database called AcctTask. In this table I have fields called 'task', 'status', 'start_date'. What I need is a module that will run through the table and kickout a total of a specific task and status for each day in March.

eg: TASK STATUS START DATE TOTAL RECORDS
===== ======= ========= ============
2000 OPEN 03/01/2005 2497
2000 OPEN 03/02/2005 1547
2000 OPEN 03/03/2005 854

etc....

Any help would be very much appreciated!!

 

[ecrespol]

I'm a bit confused as to what you are trying to do?

 

[MarkK]

Function GetStuff(SomeDate as Date) as DAO.Recordset
   Dim rst as DAO.Recordset

   Set rst = CurrentDb.OpenRecordset( _
      "SELECT Task, Status, Count(YourTableID) AS ItemCount " & _
      "FROM YourTable " & _
      "WHERE Month(StartDate) = " & Month(SomeDate) & " " & _
      "GROUP BY Task, Status;")
   Set GetStuff = rst

End Function

 

[modest]

I'm also confused with what you want?? Maybe..

Function GetTotal(strTask As String, strStatus As String, dtDate As Date) As Long
    Dim db     As DAO.Database
    Dim rs     As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT " & _
                     "Task, Status, Count(SomeOtherField) As NumOfRcds " & _
             "FROM " & _
                     "[Table Name] " & _
             "WHERE (" & _
                     "Task = "                & strTask       & " AND " & _
                     "Status = "              & strStatus     & " AND " & _
                     "Month([Start Date]) = " & Month(dtDate) & ") "    & _
             "GROUP BY " & _
                     "Task, Status;"                     

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    GetTotal = rs!NumOfRcds

    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Function