[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 908
  • Last Modified:

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?
0
Victor Kimura
Asked:
Victor Kimura
1 Solution
 
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
 
Ken ButtersCommented:
BillItemLine.[ItemLineSeqNo])='9'

is sequence number a character?... try removing single quotes from the 9.
0
 
Victor KimuraAuthor Commented:
Hi buttersk, yes, that was it. Didn't catch it. Thank you both for your input.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now