RecordSet2与RecordSet DAO.Recordset不匹配

Question

假日数据库

我需要知道某个部门有多少人请假，这样新的假期申请就可以“批准”或“拒绝”。

表This is the Table_Add_Holidays

部门Test Data with 2 departments的数据

已批准的Just sample Data的数据

我使用一个查询来执行它，它工作了，然后当我在其他地方更改了其他代码后，它停止工作。所以我使用上面的代码从头开始，但这并不能像我想象的那样工作。

我想先按“已批准”筛选数据，然后再按“部门”筛选，这样我就可以得到部门中有多少人休假的记录。

如有任何帮助，将不胜感激。

' Dim rstQuery As DAO.Recordset
' Dim rstQuery2 As DAO.Recordset

Dim rstQuery As DAO.Recordset2
Dim rstQuery2 As DAO.Recordset2

On Error GoTo ErrorHandler

'   I was using a Query but when I tried "Set rstQuery = rstQuery2.OpenRecordset" I had a mis match
'   rstQuery  - was RecordSet
'   rstQuery2 - whilst this was was RecordSet2
'
'    Set rstQuery2 = CurrentDb.OpenRecordset("SELECT * FROM [Query_Table_Add_Holidays_CountOff_General]")

Set rstQuery2 = CurrentDb.OpenRecordset("SELECT * FROM [Table_Add_Holidays]")

rstQuery2.Filter = "StatusOfRequest = 'Approved'"
FindRecordCount = rstQuery2.RecordCount

rstQuery2.Filter = "Department = '" & oDept & "'"
FindRecordCount = rstQuery2.RecordCount

' Set rstQuery = rstQuery2.OpenRecordset
Set rstQuery = rstQuery2


If rstQuery.EOF Then
    FindRecordCount = 0
Else
    rstQuery.MoveLast
    FindRecordCount = rstQuery.RecordCount
End If

rstQuery.Close

Set rstQuery = Nothing

Answer 1

你可以使用DCount

HolidayCount = DCount("*", "[Table_Add_Holidays]", "StatusOfRequest = 'Approved' And Department = '" & oDept & "'")