(Solved) – VBA error matching date from table and date from query when date has leading zero eg 01/04 or 02/04

(solved)-–-vba-error-matching-date-from-table-and-date-from-query-when-date-has-leading-zero-eg-01/04-or-02/04

I have a query in MS Access called qryRMCountStudentsBySessionWP. The results are shown below.

enter image description here

I have some VBA code shown below. Basically if the Timetable_Date, Timetable_Session and Location match Exam_Date, Exam_Session and Exam_Location of a record in a table called Invigilation, then changes are made to that record’s start time.

This works fabulously for 30/03/2020 and 31/03/2020 but not any of the April dates. I think this is because of leading zeros but I just can’t figure out how to fix it. Any ideas?

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

Leave a Reply

Your email address will not be published. Required fields are marked *