Search code examples
sqlms-accesssql-update

How to avoid receiving "Enter Parameter Value" when using access to update certain data

I am trying to execute the following sql 

UPDATE (SELECT a.Volumn, b.Out_1 FROM A_Temp as a,Volume as b
WHERE a.Dates = b.Dates 
AND b.COLUMN_NAME LIKE '%Out_1%')
SET a.Volumn = b.Out_1

I want to find a column with name 'Out_1' and update the data of this column into another table. But each time, I was asked to enter b.column_name. How can I avoid this set?

I plan further to use matlab automatically exec all sqls.

Thanks,

Solution

  • Are you trying to search the column titles for a column with a name like %Out_1%? If that is the case it will not work because your query is treating b.COLUMN_NAME as if that is the name of your column.

    When you enter the name of a column that does not exist in a query, access assumes it is a parameter and asks you if you want to enter a value. If you are trying to update a column in one table with a column in another table you will require a query that performs a JOIN.

    UPDATE [A_Temp] INNER JOIN Volume ON [A_Temp].Dates = Volume.Dates SET [A_Temp].Volumn = Volume.[Out_1]