Recording Date using VBA

[ewong1]

I currently have a field set up on a Schedule Form(frmSchedule) that requires a user to enter in a Schedule Date (dtmSchedule). In the end, the form is supposed to check to see if there are any records in the Equipment Schedule Table (tblEquipmentSchedule) - if there are no records with that date, it is to add a record to the table with the date entered by the user. I have provided the code below.

Private Sub dtmSchedule_AfterUpdate()
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Dim strSQL As String
    strSQL = "SELECT dtmSchedule FROM qryScheduleDates"
    
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    Dim intRecordCount As Integer

    'Determine if Schedule has already been created for the date provided in "dtmSchedule" field
    intRecordCount = 0
    rs.MoveFirst
    
    Dim dtmScheduleFinal As String
    dtmScheduleFinal = dtmSchedule
    MsgBox dtmScheduleFinal, vbOKOnly
    
    Do Until rs.EOF Or intRecordCount = 1
    
        If rs!dtmSchedule = Me.dtmSchedule Then
            intRecordCount = intRecordCount + 1
        Else
        End If
        rs.MoveNext
    Loop
    
    If intRecordCount = 0 Then
        If MsgBox("There are no Schedules created for the date you have entered.  Would you like to create a blank one now?", vbYesNo, "Create New Schedule?") = vbYes Then
            DoCmd.RunSQL ("Insert Into tblEquipmentSchedule(dtmSchedule) Values(" & dtmScheduleFinal & ")")
            Forms!frmSchedule!cboScheduleDate.Requery
        Else
            Forms!frmSchedule!cboScheduleDate = dtmSchedule
        End If
    Else
    End If
End Sub

I've gotten everything to work up until the point of the record addition to the Equipment Schedule Table(tblEquipmentSchedule). Currently, the date that is entered defaults to the usual 12/30/1899. Any ideas on how I can get the system to add the record with the date actually entered by the users?

 

[ewong1]

Got it! I was able to solve this issue by including # signs on both sides of the date variable.

DoCmd.RunSQL ("Insert Into tblEquipmentSchedule(dtmSchedule) Values(#" & dtmSchedule & "#)")

 

[namliam]

You may want to force american format in your SQL
dtmSchedule => Format(dtmSchedule,"mm/dd/yyyy")
to prevent any future problems with days and months beeing switched.