Troubleshooting - Update from a select statement not working

Problem Description:

I am trying to update records through the select statement. But it is not working.

UPDATE Customer SET CustomFieldProjectNumber = i.JobNumber FROM ( SELECT ListID, {fn SUBSTRING("Name", 1, 5)} AS JobNumber FROM Customer WHERE TimeCreated > {ts '2016-01-01 00:00:00.000'} ) i WHERE i.ListID = Customer.ListID

I am getting "Unexpected extra token: (" error in VB Demo.

Any Assistance Is Appreciated.  

Solution:

You can execute below query in MS Access linked table without any issue.

Please refer How to Use QODBC with MS Access 2016 / 365 / 2013 32-bit

You need to link Customer table in MS Access & execute the query in access query editor.

MS Access>> create tab >> Query Design >> Paste query >> Execute query using Run button

update Customer qe inner join Customer de on qe.ListID = de.ListID set qe.CustomFieldProjectNumber = MID (de.Name,1,5) WHERE qe.TimeCreated >#2016-01-01 00:00:01#