MS Access data type mismatch error

Hi,

I run this code in MS Access:
Sub UpdateX()

    Dim dbs As Database

    Set dbs = OpenDatabase("F:\BackUp\ClientsWebsite\OMusicStudios\BackUp\BackUp_G_USB\Administration\Quickbooks\MSAccess\qb_export_12_30_11_2101.accdb")
   
    ' Update BillItemLine table
   
        dbs.Execute "UPDATE BillItemLine " _
        & "SET ItemLineCustomerRefListID = '80000431-1322772965', ItemLineCustomerRefFullName = 'Lee, Wendy' " _
        & "WHERE (((BillItemLine.[VendorRefFullName]) Like '*sun*') " _
        & "AND ((BillItemLine.[TxnID])='20A8D-1325181637') AND " _
        & "((BillItemLine.[ItemLineSeqNo])='9') );"
       
    dbs.Close

End Sub

but I get a data type mismatch error. Both ItemLineCustomerRefListID  and ItemLineCustomerRefFullName  are string data types.

What could be the problem?
Victor KimuraSEO, Web DeveloperAsked:
Who is Participating?
 
Ken ButtersConnect With a Mentor Commented:
BillItemLine.[ItemLineSeqNo])='9'

is sequence number a character?... try removing single quotes from the 9.
0
 
YZlatCommented:
make sure the spacing is not a problem. Try

 dbs.Execute "UPDATE BillItemLine " _
        & "SET ItemLineCustomerRefListID = '80000431-1322772965', ItemLineCustomerRefFullName = 'Lee, Wendy'  " _
        & "WHERE (((BillItemLine.[VendorRefFullName]) Like '*sun*')  " _
        & "AND ((BillItemLine.[TxnID])='20A8D-1325181637') AND  " _
        & "((BillItemLine.[ItemLineSeqNo])='9') );"

Open in new window

0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Hi buttersk, yes, that was it. Didn't catch it. Thank you both for your input.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.