Inserting current date in Microsoft Access database?

Question

When trying to use the DateString to add a date to a microsoft access database, I get the "Data type mismatch in criteria expression" error. The documentation states that "DateString returns the system date in the MM-dd-yyyy format". In the access database, I have set the format to "short date", which has an input mask like so.

The reason I suspect this problem being related to the date is that inserting the data using this method, but forgoing the date, works fine. Below is my code, if anybody has any ideas for why this might be occurring I would greatly appreciate it.

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    'Change the following to your access database location
    dataFile = "C:\Users\Administrator\Documents\CampbellBonuses.accdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString

    myConnection.Open()
    Dim str As String
    str = "insert into EmployeeBonus ([EID], [IncentiveID], [ProcedureDate], [PatientFirstName], [PatientLastName], [PatientID]) values (?, ?, ?, ?, ?,?)"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    cmd.Parameters.Add(New OleDbParameter("EID", CType(ComboBox1.ValueMember, String)))
    cmd.Parameters.Add(New OleDbParameter("IncentiveID", CType(ComboBox2.ValueMember, String)))
    cmd.Parameters.Add(New OleDbParameter("ProcedureDate", DateString))
    cmd.Parameters.Add(New OleDbParameter("PatientFirstName", CType(txtPFname.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("PatientLastName", CType(txtPLname.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("PatientID", CType(TextBox1.Text, String)))
    Try
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        myConnection.Close()
        'TextBox1.Clear()
        'TextBox2.Clear()
        'TextBox3.Clear()
        'TextBox4.Clear()
        'TextBox5.Clear()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub

Gustav Gustav · Accepted Answer · 2018-06-20T06:41:53

The format in the table has no importance; it is for default display only.

You could either let Access insert the current date:

str = "insert into EmployeeBonus ([EID], [IncentiveID], [ProcedureDate], [PatientFirstName], [PatientLastName], [PatientID]) values (?, Date(), ?, ?, ?, ?)"

or - in case of concatenating - provide a, for Access, valid string expression for the date:

DateString = DateTime.Today.ToString("'#'yyyy'/'MM'/'dd'#'");

Edit:

However, as noted by Eric, specify the parameter as DateTime and pass the true date value:

DateTime.Today

Inserting current date in Microsoft Access database?

1 Answers