locked
Problem with Access Update query RRS feed

  • Question

  • Hello developers,

    I am using following update query but i am getting "Run time error 3464 Data type mismatch in criteria expression".

     

    Field "PM ID" in table prod is text data type and has id's with integer and text as well. for eg 01278878, P9875678. I am using update query to delete first integer or letter from id's.

    Sub qry2()
    Dim objdb As Database
    Dim strsql As String
    strsql = " UPDATE prod SET prod.[PM ID] = Mid([PM ID],2)" _
    & "WHERE ((Left([PM ID],1)=0))"
    Debug.Print strsql

    Set objdb = CurrentDb()
    objdb.Execute strsql, dbFailOnError
    Set objdb = Nothing
    End Sub

    sql from immediate window

     UPDATE Financials SET Financials.[PPM ID] = Mid([PPM ID],2)WHERE ((Left([PPM ID],1)=0))

     

    Friday, September 23, 2011 3:47 PM

Answers

  • Do you want to update the string removing the first charachter?

    Assuming that the string length is 8, then you can do something like this:

    Sub qry2()
    Dim objdb As Database
    Dim strsql As String
    strsql = "UPDATE prod SET prod.[PM ID] = Mid([PM ID],2,7)"
    Debug.Print strsql
    
    Set objdb = CurrentDb()
    objdb.Execute strsql, dbFailOnError
    Set objdb = Nothing
    End Sub
    


    NOTE: In your first SQL statement you added a WHERE clause, where you only want to remove the one starting with a 0.

    That is different then removing the first charachter with no condition set.

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by Bruce Song Thursday, October 6, 2011 8:43 AM
    Friday, September 23, 2011 5:20 PM
  • strsql = " UPDATE prod SET prod.[PM ID] = Mid([PM ID],2)" _
    & " WHERE ((Left([PM ID],1)=""0""))"

    Using LEFT on a value means the return value is a STRING, not a number.  So you must surround the 0 with quote marks.


    John Spencer Access MVP 2002-2005, 2007-2011
    • Marked as answer by Bruce Song Thursday, October 6, 2011 8:43 AM
    Friday, September 23, 2011 6:58 PM

All replies

  • Try add another space in the SQL after the Mid() method, so it will something like this:

    Sub qry2()
    Dim objdb As Database
    Dim strsql As String
    strsql = " UPDATE prod SET prod.[PM ID] = Mid([PM ID],2) " _
    & "WHERE ((Left([PM ID],1)=0))"
    Debug.Print strsql
    
    Set objdb = CurrentDb()
    objdb.Execute strsql, dbFailOnError
    Set objdb = Nothing
    End Sub
    
    sql from imm
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, September 23, 2011 3:51 PM
  • Thanks for responding,

    keeping space after Mid gives same error.

    Friday, September 23, 2011 5:10 PM
  • Do you want to update the string removing the first charachter?

    Assuming that the string length is 8, then you can do something like this:

    Sub qry2()
    Dim objdb As Database
    Dim strsql As String
    strsql = "UPDATE prod SET prod.[PM ID] = Mid([PM ID],2,7)"
    Debug.Print strsql
    
    Set objdb = CurrentDb()
    objdb.Execute strsql, dbFailOnError
    Set objdb = Nothing
    End Sub
    


    NOTE: In your first SQL statement you added a WHERE clause, where you only want to remove the one starting with a 0.

    That is different then removing the first charachter with no condition set.

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by Bruce Song Thursday, October 6, 2011 8:43 AM
    Friday, September 23, 2011 5:20 PM
  • Field "PM ID" in table

    strsql = " UPDATE prod SET prod.[PM ID] = Mid([PM ID],2)" _
    & "WHERE ((Left([PM ID],1)=0))"

     UPDATE Financials SET Financials.[PPM ID] = Mid([PPM ID],2)WHERE ((Left([PPM ID],1)=0))

    Which is it -- [PM ID] or [PPM ID]?
    Friday, September 23, 2011 5:41 PM
  • strsql = " UPDATE prod SET prod.[PM ID] = Mid([PM ID],2)" _
    & " WHERE ((Left([PM ID],1)=""0""))"

    Using LEFT on a value means the return value is a STRING, not a number.  So you must surround the 0 with quote marks.


    John Spencer Access MVP 2002-2005, 2007-2011
    • Marked as answer by Bruce Song Thursday, October 6, 2011 8:43 AM
    Friday, September 23, 2011 6:58 PM