MS Access data type mismatch error

Posted on 2012-09-07
Last Modified: 2012-09-07

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') );"

End Sub

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

What could be the problem?
Question by:Victor Kimura
    LVL 35

    Expert Comment

    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

    LVL 19

    Accepted Solution


    is sequence number a character?... try removing single quotes from the 9.

    Author Comment

    by:Victor Kimura
    Hi buttersk, yes, that was it. Didn't catch it. Thank you both for your input.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Suggested Solutions

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now