none
Type Mismatch Error - Access

    Question

  • I wrote an SQL query in Excel that I was using as a data source. I now think it would be better to use access as a data source. I copy-pasted my query (both excel and access are hooked up to my sql server database) and now I get a type mismatch error when trying to match two ID's. My query is as follows:

    SELECT ASSET_TAG, Q.ID, ASSET_NAME, SERIAL_NUMBER, Q.STATUS, MANUFACTURER, PRODUCT_NAME, CA.CASE_ID, C.CASE_REFERENCE, C.OPERATION_NAME, C.CASE_DESCRIPTION, CON.COMPANY, EX.EXHIBIT_REFERENCE
    FROM ((((DBO_EQUIPMENT_HARDWARE_QUANTITIES Q LEFT JOIN DBO_EQUIPMENT_HARDWARE H
    ON Q.HARDWARE_ID = H.ID) LEFT JOIN DBO_CASE_ASSETS CA
    ON CA.ASSET_ID = Q.ID) LEFT JOIN DBO_CASES C
    ON C.ID = CA.CASE_ID) LEFT JOIN DBO_CONTACTS CON
    ON CON.ID = C.CONTACT_ID) LEFT JOIN DBO_CASE_EXHIBITS EX
    ON EX.ID = CA.EXHIBIT_ID
    ORDER BY Q.ASSET_TAG

    With a little bit of binary-search debugging I narrowed it down to  CA.ASSET_ID = Q.ID being the issue. Does anyone know what would be causing this, or how to fix it?

    Friday, January 13, 2012 10:53 PM

Answers

  • Have you checked the data types for those fields in the relevant tables?
    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Simo2781 Monday, January 16, 2012 11:25 PM
    Saturday, January 14, 2012 11:43 PM

All replies

  • Have you checked the data types for those fields in the relevant tables?
    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Simo2781 Monday, January 16, 2012 11:25 PM
    Saturday, January 14, 2012 11:43 PM
  • ...Wow...

    Ya, it's auto-number vs text. And now I feel stupid.

    Monday, January 16, 2012 10:02 PM