我正在尝试使用VBA代码跳过假期。我已经编写了代码，并且可以在工作日使用它，所以我只在周一至周五提款。该代码旨在提取“前一天”总数，因此我总是落后1天，但是我的问题是，当有一个假期时，该代码会跳过假期，将所有0设为零，我需要做的就是使这一天为零。
该代码将逐步遍历该代码，以查找我迄今为止整周收集的7个范围内的记录数，但是如果包含一个假期，则有可能我们可以在该范围内有2个假期将这些天显示为记录（我在数据库中输入空记录）。有没有一种方法可以让我否定空值，并让它跳过应该在工作日所在的数据库中的空值，或者我可以创建一个子例程从具有假期的表中拉出子程序，而仅跳过那些仍显示5个工作日的天。
Dim iWeekDay As Integer
Dim sSQL As String
Dim objRecordset As ADODB.Recordset
Dim objRecordsetConn As ADODB.Connection
Dim objRecordsetComm As ADODB.Command
iWeekDay = WeekDay(Date)
Set objRecordsetConn = New ADODB.Connection
objRecordsetConn.ConnectionString = CurrentProject.Connection
objRecordsetConn.Open
'Start new section Collections
sSQL = "Select CollDate From tblTXandVACollDist Where CollDate Between date()-7 and date()-1 Group by CollDate Order by CollDate;"
Set objRecordsetComm = New ADODB.Command
objRecordsetComm.ActiveConnection = objRecordsetConn
objRecordsetComm.CommandText = sSQL
objRecordsetComm.CommandType = adCmdText
'Open ADO recordset
Set objRecordset = New ADODB.Recordset
objRecordset.ActiveConnection = objRecordsetConn
Set objRecordset.Source = objRecordsetComm
objRecordset.CursorLocation = adUseClient
objRecordset.CursorType = adOpenStatic 'Allows both forward and backward movement in recordset
objRecordset.LockType = adLockPessimistic 'Allows records to be edited.
objRecordset.Open
'objRecordset.MoveLast
'objRecordset.MoveFirst
If objRecordset.RecordCount >= 1 Then
dCollDate1 = objRecordset("CollDate")
Else
Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
Case 2 ' This is if the current day is monday
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 3
Case 3 ' This is if the current day is Tue
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 1
Case 4 ' This is if the current day is Wend
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 5 ' This is if the current day is Thur
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 6 ' This is if the current day is Fri
dCollDate1 = Date - 7
dCollDate2 = Date - 4
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
End Select
End If
If objRecordset.RecordCount >= 2 Then
objRecordset.MoveNext
dCollDate2 = objRecordset("CollDate")
Else
Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
Case 2 ' This is if the current day is monday
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 3
Case 3 ' This is if the current day is Tue
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 1
Case 4 ' This is if the current day is Wend
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 5 ' This is if the current day is Thur
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 6 ' This is if the current day is Fri
dCollDate1 = Date - 7
dCollDate2 = Date - 4
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
End Select
End If
If objRecordset.RecordCount >= 3 Then
objRecordset.MoveNext
dCollDate3 = objRecordset("CollDate")
Else
Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
Case 2 ' This is if the current day is monday
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 3
Case 3 ' This is if the current day is Tue
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 1
Case 4 ' This is if the current day is Wend
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 5 ' This is if the current day is Thur
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 6 ' This is if the current day is Fri
dCollDate1 = Date - 7
dCollDate2 = Date - 4
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
End Select
End If
If objRecordset.RecordCount >= 4 Then
objRecordset.MoveNext
dCollDate4 = objRecordset("CollDate")
Else
Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
Case 2 ' This is if the current day is monday
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 3
Case 3 ' This is if the current day is Tue
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 1
Case 4 ' This is if the current day is Wend
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 5 ' This is if the current day is Thur
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 6 ' This is if the current day is Fri
dCollDate1 = Date - 7
dCollDate2 = Date - 4
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
End Select
End If
If objRecordset.RecordCount = 5 Then 'this is a test portion to see if i have to adjust the chart to make sure the chart works on an individual basis per day
objRecordset.MoveNext
dCollDate5 = objRecordset("CollDate")
Else
Select Case iWeekDay ' This is if there are no collection totals form the table the report will search for the current date and then use the below case to set the days so all other information that is pulled in will match with the collection date.
Case 2 ' This is if the current day is monday
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 3
Case 3 ' This is if the current day is Tue
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 4
dCollDate5 = Date - 1
Case 4 ' This is if the current day is Wend
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 5
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 5 ' This is if the current day is Thur
dCollDate1 = Date - 7
dCollDate2 = Date - 6
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
Case 6 ' This is if the current day is Fri
dCollDate1 = Date - 7
dCollDate2 = Date - 4
dCollDate3 = Date - 3
dCollDate4 = Date - 2
dCollDate5 = Date - 1
End Select
End If
最佳答案
我最初的想法是您可以执行以下查询：
Select MAX(CollDate) FROM tblTXandVACollDist WHERE [otherfield] <> 0 AND (CollDate <> (select max(CollDate) FROM tblTXandVACollDist [otherfield] <> 0)) )
取决于您使用的数据库的语法可能有所不同，但是类似这样的查询会告诉数据库给您最后一个日期，即今天您要与之比较的字段不等于0的日期。
然后，您可以将其更改为“ today-2”，“ today-3”等，这样可以减少您不得不在其中使用的许多逻辑。 （还可以使用此查询，您可能更容易修改它。取决于您的数据库）
Select MAX(CollDate) FROM tblTXandVACollDist WHERE [otherfield] <> 0 AND (CollDate <> (select TOP 1 CollDate FROM tblTXandVACollDist WHERE [otherfield] <> 0 ORDER BY CollDate))
并将其更改为最近两天的折扣
Select MAX(CollDate) FROM tblTXandVACollDist WHERE [otherfield] <> 0 AND (CollDate not IN(select TOP 2 CollDate FROM tblTXandVACollDist WHERE [otherfield] <> 0 ORDER BY CollDate))
关于mysql - 在 Access 中拉出日期范围并跳过假期，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41578325/