Update Query with Dlookup - Type Conversion Error RRS feed

  • Question

  • Hi, I am building an order management platform and orders are received in batches from an xls file.  I have been able to import the file successfully to a new table ImportTR, but I am unable to get the ClientID from the ClientT table to sync.  Company_Name on the ImportTR table will match CompanyName from the ClientT table so I used that as my reference field.

    Notable DB structure:
    Table: ImportTR
    - Field: ClientID_FK (Number)
    - Field: Company_Name (Short Text)
    Table: ClientT
    - Field: ClientID (Number, No duplicates, Primary Key)
    - Field: CompanyName (Short Text)

    In the update query, I have one field only, setup as follows:
    Field: CompanyID_FK
    Table: ImportTR
    Update To: DLookUp("[ClientID]","ClientT","[CompanyName]=" & [ImportTR]![Company_Name])
    Criteria: Is Null

    Upon running the query I receive the following error message and do not know why:

    Microsoft Access can't update all the records in your update query.

    Microsoft Access didn't update 13 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
    Do you want to continue running this type of action query anyway?
    To ignore the error(s) and run the query, click Yes.
    For an explanation of the causes of the violations, click Help.

    (Help goes to this path: Access/Queries/Advanced queries/Common errors when you run an append query)

    I have clicked Yes, and nothing is updated.  I have clicked Help and it gave reasons that there might be a conflict but I got past all the possibilities without needing any changes (as far as I can tell).  As you might see, I seem to be in a pickle here.  Any help you can provide is GREATLY appreciated.

    Lastly, there is only 13 records to update at this time because that is sample data but day to day use will be scaled quite a bit.
    And as for the xls file, I am unable to control how I receive it and I know I could likely add a column with a vlookup to get the client ID from another source but I am trying to automate as much as possible, so other users can use this with minimal trouble.

    Thank you.

    Thursday, January 4, 2018 6:36 PM

All replies

  • Hi,

    I don't think your DLookup() is retrieving any value. My guess is it is returning Null values, which can't be converted into a Number; hence the error.

    Instead, try the following query:

    UPDATE ImportTR INNER JOIN ClientT ON ImportTR.Company_Name=ClientT.CompanyName SET CompanyID_FK=ClientID

    PS. Not sure which is the correct name of the field to update because you called it ClientID_FK in your table description above but then called it CompanyID_FK in the query description.

    Hope it helps...

    Thursday, January 4, 2018 6:59 PM
  • I will give it a go, thank you!  And yeah, type-o on my part.  It is clientID_FK.
    Thursday, January 4, 2018 7:02 PM
  • This worked brilliantly, thank you so much.  I need to find time to get up to speed on my SQL / VBA
    Thursday, January 4, 2018 7:19 PM
  • Hi,

    You're welcome. Good luck with your project.

    Thursday, January 4, 2018 7:21 PM
  • Hello Kristoffer Valuekis,

    I'm glad to hear that your issue is resolved and I would suggest you mark helpful reply to help other developers use this forum efficiently. If you have any other issue, please feel free to post new thread to let use know.

    Thanks for understanding.

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 5, 2018 5:47 AM