当日期前导零时，VBA错误匹配表中的日期和查询中的日期，例如01/04或02/04

我在MS Access中有一个名为qryRMCountStudentsBySessionWP的查询。结果如下所示。

我有一些下面显示的VBA代码。基本上，如果Timetable_Date，Timetable_Session和Location与称为Invigilation的表中记录的Exam_Date，Exam_Session和Exam_Location相匹配，则对该记录的开始时间进行更改。

这在30/03/2020和31/03/2020上非常有效，但在4月的任何日期中都没有。我认为这是因为前导零，但我只是想不出如何解决它。有任何想法吗？

Private Sub wordProcessing()

    Dim dbs                As Database
    Dim name               As String
    Dim SQL                As String
    Dim rstAllSessions     As Recordset

    Set dbs = CurrentDb

         SQL = "SELECT Timetable_Date, Timetable_Session, Location, CountOfStudent_Ref FROM qryRMCountStudentsBySessionWP;"


    Set rstAllSessions = dbs.OpenRecordset(SQL)

    rstAllSessions.MoveFirst

    Do Until rstAllSessions.EOF  ' for each sesson update invigilation table


         If rstAllSessions.Fields(3) >= 10 Then

            If rstAllSessions.Fields(1) = "A" Then
                dbs.Execute "UPDATE Invigilation SET Start_Time = TimeValue('08:30:00'), Notes = 'WP Exam with 10 or more students' WHERE Invigilation.Exam_Date = #" & rstAllSessions!Timetable_Date & "# AND Invigilation.Exam_Location = " & "'" & rstAllSessions!Location & "'" & "AND  Invigilation.Exam_Session = " & "'" & rstAllSessions!Timetable_Session & "'" & ";", dbFailOnError
            Else
                dbs.Execute "UPDATE Invigilation SET Start_Time = TimeValue('13:00:00'), Notes = 'WP Exam with 10 or more students' WHERE Invigilation.Exam_Date = #" & rstAllSessions!Timetable_Date & "# AND Invigilation.Exam_Location = " & "'" & rstAllSessions!Location & "'" & "AND  Invigilation.Exam_Session = " & "'" & rstAllSessions!Timetable_Session & "'" & ";", dbFailOnError
            End If

         ElseIf rstAllSessions.Fields(3) >= 5 Then

            If rstAllSessions.Fields(1) = "A" Then
                dbs.Execute "UPDATE Invigilation SET Start_Time = TimeValue('08:35:00'), Notes = 'WP Exam with 5 or more students' WHERE Invigilation.Exam_Date = #" & rstAllSessions!Timetable_Date & "# AND Invigilation.Exam_Location = " & "'" & rstAllSessions!Location & "'" & "AND  Invigilation.Exam_Session = " & "'" & rstAllSessions!Timetable_Session & "'" & ";", dbFailOnError
            Else
                dbs.Execute "UPDATE Invigilation SET Start_Time = TimeValue('13:05:00'), Notes = 'WP Exam with 5 or more students' WHERE Invigilation.Exam_Date = #" & rstAllSessions!Timetable_Date & "# AND Invigilation.Exam_Location = " & "'" & rstAllSessions!Location & "'" & "AND  Invigilation.Exam_Session = " & "'" & rstAllSessions!Timetable_Session & "'" & ";", dbFailOnError
            End If

        ElseIf rstAllSessions.Fields(3) > 1 Then

            If rstAllSessions.Fields(1) = "A" Then
                dbs.Execute "UPDATE Invigilation SET Start_Time = TimeValue('08:40:00'), Notes = 'WP Exam with >1 and <5 students' WHERE Invigilation.Exam_Date = #" & rstAllSessions!Timetable_Date & "# AND Invigilation.Exam_Location = " & "'" & rstAllSessions!Location & "'" & "AND  Invigilation.Exam_Session = " & "'" & rstAllSessions!Timetable_Session & "'" & ";", dbFailOnError
            Else
                dbs.Execute "UPDATE Invigilation SET Start_Time = TimeValue('13:10:00'), Notes = 'WP Exam with >1 and <5 students' WHERE Invigilation.Exam_Date = #" & rstAllSessions!Timetable_Date & "# AND Invigilation.Exam_Location = " & "'" & rstAllSessions!Location & "'" & "AND  Invigilation.Exam_Session = " & "'" & rstAllSessions!Timetable_Session & "'" & ";", dbFailOnError
            End If

        Else

           If rstAllSessions.Fields(1) = "A" Then
                dbs.Execute "UPDATE Invigilation SET Start_Time = TimeValue('08:45:00'), Notes = 'WP Exam with 1 student' WHERE Invigilation.Exam_Date = #" & rstAllSessions!Timetable_Date & "# AND Invigilation.Exam_Location = " & "'" & rstAllSessions!Location & "'" & "AND  Invigilation.Exam_Session = " & "'" & rstAllSessions!Timetable_Session & "'" & ";", dbFailOnError
            Else
                dbs.Execute "UPDATE Invigilation SET Start_Time = TimeValue('13:15:00'), Notes = 'WP Exam with 1 student' WHERE Invigilation.Exam_Date = #" & rstAllSessions!Timetable_Date & "# AND Invigilation.Exam_Location = " & "'" & rstAllSessions!Location & "'" & "AND  Invigilation.Exam_Session = " & "'" & rstAllSessions!Timetable_Session & "'" & ";", dbFailOnError
            End If

        End If

       '  doesn't do dates with leading 0s?????

       rstAllSessions.MoveNext

    Loop

    rstAllSessions.Close
    dbs.Close


End Sub