RecordSet2与RecordSet DAO.RecordSet不匹配

Question

假日数据库我需要知道某个部门有多少人休假，所以新的假期请求可以是"休假"；批准"；或"；被拒绝"；

表这是Table_Add_Holidays

部门数据两个部门的测试数据

批准的数据仅样本数据

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

我想通过"；批准"；先然后我想通过"；部门"；这样我就可以记录下部门有多少人休假

任何帮助都将不胜感激TIA-

---

' 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 & "'")