Update A multi-valued field in Access
If you're adding a value to your multi-valued field, use an append query.
INSERT INTO table_name( [column_name].Value )VALUES (55)WHERE ID = 16;
If you want to change one particular value which exists in your multi-valued field, use an UPDATE statement. For example, to change the 55 to 56 ...
UPDATE [table_name]SET [column_name].Value = 56WHERE [column_name].Value = 55 And ID = 16;
See Using multivalued fields in queries for more information.
I have figured this out! It certainly was counter-intuitive! You have to use an INSERT statement to do the update.
-- Update a record with a multi-valued field that has no valueINSERT INTO [table_name] ( [[column_name].[Value] )VALUES(55)WHERE [table_name].ID = 16;
This confused me because I was expecting an UPDATE statement. I think it actually inserts a record into a hidden table that is used to associate multiple values with this column.
I am working with Sharepoint, I created the tables as multi-value fields, ran into the error with my
INSERT INTO statement, went back to Sharepoint to change to non-multi-value fields, but that didn't fix it.
Recreated the table without using multi-value fields, and the
INSERT INTO worked just fine.