Update a text Field's value based on another date field passing a certain date

Question

For my Access database, I have a singular table with two fields - Award Status and Proposed Due Date. I want the Award Status to change to "Pending" once the Proposed Due Date has passed.

Currently, I am writing VBA to have the field update once the criteria is met.

Function AwardUpdate()

    Dim rstInput As DAO.Recordset
    Set rstInput = CurrentDb.OpenRecordset("TestInput")

    With rstInput
        Do Until .EOF
            .Edit
            If .Fields(19) < Date And .Fields(29) = "Pre-Submission" Then
                .Fields(29) = "Pending"
                .MoveNext
            Else
                .MoveNext
            End If
        Loop
    End With

End Function

But I can't seem to get the criteria to check for if the Proposed Due Date is less than the current date.

Is there a better way to accomplish this?

Answer 1

This should work - only editing where needed:

   Do Until .EOF
       If .Fields(19).Value < Date And .Fields(29).Value = "Pre-Submission" Then
           .Edit
               .Fields(29).Value = "Pending"
           .Update
       End If
       .MoveNext
   Loop