当记录存在时访问VBA recordCount返回0

问题描述：

我正在尝试创建一个函数来将n个工作日添加到日期。这显然需要考虑周末和假期。周末部分工作正常，但我用表格列出所有的假期，我无法让我的查询拉精确的recordCount。它坚持计数总是零。这里是我的代码：当记录存在时访问VBA recordCount返回0

Public Function dateAddNoWeekends(dt1 As Date, genDate As Integer) 
Dim i As Integer 
Dim date1 As Date 
Dim date2 As Date 
Dim holidays As Boolean 
Dim isWeekday As Boolean 
Dim dayOfWeek As Integer 
Dim numDays As Integer 
Dim db As Database 
Dim rs As DAO.Recordset 
Dim sql As String 
Set db = CurrentDb 
date1 = dt1 
date2 = date1 
i = 0 
numDays = genDate 

sql = "SELECT * FROM Holidays WHERE holidayDate = " & date2 
Set rs = db.OpenRecordset(sql) 

Debug.Print date2 

Do While i < numDays 
    If Not isWeekday Then date2 = date2 + 1 
    dayOfWeek = Weekday(date2) 

    If rs.RecordCount > 0 Then 
     Debug.Print rs.RecordCount 
     holidays = True 
    Else 
     holidays = False 
     Debug.Print rs.RecordCount 
    End If 

    If dayOfWeek > 1 And dayOfWeek < 7 And holidays = False Then 
     If i < numDays - 1 Then date2 = (date2 + 1) 
      isWeekday = True 
      i = i + 1 
     Else: isWeekday = False 
     End If 
Loop 

rs.Close 
Debug.Print date2

也许是日期格式的问题？尝试使用“2017-12-25”等常数日期进行测试。 – Stefan

是否这样？ sql =“SELECT * FROM Holidays WHERE holidayDate ='7/1/2017'”这会导致数据类型不匹配。 – Eleyna

'Set rs = db.OpenRecordset（sql）'add'rs.MoveLast'后面。也可能''SELECT * FROM Holidays WHERE holidayDate =“＆CLNG（date2）'。我讨厌在Access中的日期。 :) –

答

为了解决这个问题，我宣布在循环顶部的变量（所以它会每次更新正确），并分配一个DCOUNT函数从date2的数到DATE2。

当记录存在时访问VBA recordCount返回0

相关推荐