1 Replies - 1145 Views - Last Post: 21 September 2014 - 10:22 AM Rate Topic: -----

#1 evinz123   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-September 14

Data type mismatch in criteria expression, please help!

Posted 21 September 2014 - 09:09 AM

I am doing a point of sale system for my course requirement. After a lot of work and research. In my MS ACCESS, I set my ItemNumber to Text format, and it was working just fine. However, I want to set the ItemNumber as Number format in my MS ACCESS, and this is where the "Data type mismatch in criteria expression" error keeps coming up. I just got stuck on the this error. I just registered here, so I'm not very familiar with the rules, so I'm sorry in advance for any wrong I might have committed. So here's my code:

 Dim adoConn As New ADODB.Connection 'Active Data Object (ado); connects you to a database (or another app)
    Dim adoRS As New ADODB.Recordset 'RS = Record Set; takes records from table. Mura ug cursor.
    Dim strConn As String 'user defined
    strConn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = D:\Columnas\CISB41\cisbproject\Database_ryan.mdb" 'to connect to database
    'strConn = strConn & "D:\Columnas\CISB41\cisbproject\Database.mdb"
    adoConn.ConnectionString = strConn
    adoRS.Source = "SELECT * FROM INVENTORY WHERE [ItemNumber] ='" & Text1.Text & "'" 'SQL search
    adoRS.CursorType = adOpenForwardOnly
    adoRS.ActiveConnection = adoConn

    If adoRS.EOF Then
        MsgBox "Record Not Found", vbExclamation, "Search"
        Label14.Caption = adoRS.Fields("Title").Value ' & vbTab & adoRS.Fields("EmpName").Value _
                & vbTab & adoRS.Fields("Position").Value & vbTab & adoRS.Fields("Salary").Value & vbTab _
                & adoRS.Fields("DeptNo").Value
        Label15.Caption = adoRS.Fields("Author").Value
        Label22.Caption = adoRS.Fields("Price").Value
        Label16.Caption = adoRS.Fields("Quantity").Value
    End If
    'to close adoRS and adoConn
    Set adoRS = Nothing
    Set adoConn = Nothing
    Label13.Caption = Text1.Text
    Text1.Text = ""
 End Sub

I'm sorry for the tl;dr. I hope you can help.

Is This A Good Question/Topic? 0
  • +

Replies To: Data type mismatch in criteria expression, please help!

#2 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6696
  • View blog
  • Posts: 27,485
  • Joined: 12-December 12

Re: Data type mismatch in criteria expression, please help!

Posted 21 September 2014 - 10:22 AM

What is contained in Text1.Text?
If ItemNumber is a number field then the value for it doesn't need to be surrounded in apostrophes.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1