2 Replies - 744 Views - Last Post: 09 April 2012 - 12:27 PM Rate Topic: -----

#1 TechnoBear  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 222
  • Joined: 02-November 11

Data type mismatch in criteria expression.

Posted 09 April 2012 - 09:49 AM

My code follows but the issue is that when i try to run this sub i get the error: "Data type mismatch in criteria expression." at line: "command.ExecuteNonQuery()" I am trying to send a text string of "N" when the date in the Valid Until column is before today's date and update the Valid? column if this is true.

    Private Sub UpdateRecord()
        Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        Dim command As OleDb.OleDbCommand = New OleDb.OleDbCommand()

        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & m_strPath & "\database\clients.accdb;Persist Security Info=False;"
        command.Connection = con

        con.Open()

        command.CommandText = "UPDATE tblClients SET [Valid?] = @valid WHERE [Valid Until] < '" & Today.Month & "/" & Today.Day & "/" & Today.Year & "';"

        Dim parValid As New OleDb.OleDbParameter("@valid", "N".ToString)

        command.Parameters.Add(parValid)

        command.ExecuteNonQuery()

        con.Close()
    End Sub


I'm not understanding why i get the error because the receiving column is text

Is This A Good Question/Topic? 0
  • +

Replies To: Data type mismatch in criteria expression.

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Data type mismatch in criteria expression.

Posted 09 April 2012 - 11:45 AM

instead of apostrophes use # to surround a date in Access. so
[Valid Until] < #" & Today.Month & "/" & Today.Day & "/" & Today.Year & "#;"

Also, not sure what the ? is in the in the column name for the set but I'm assuming that it's OK as you would be getting a different error.
Was This Post Helpful? 1
  • +
  • -

#3 TechnoBear  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 222
  • Joined: 02-November 11

Re: Data type mismatch in criteria expression.

Posted 09 April 2012 - 12:27 PM

the ? is part of the column name and that made it work. thank you
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1