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.