Update Query on Multivalued field not working

[duthiedon]

Hello!

Have been struggling trying to find an answer on why an update query isn't working when trying to update an multivalued field. In this table, there are 647 records and only 9 of which already have a value set in the "AssociatedProject" field. More than one AssociatedProject is associated with a vendor, so this field allows for multiple entries. I've looked at all the information on update queries and updating a multivalued field, but it will only update the existing 9 records. It does not recognize the remaining ones (they have no values yet).

Here's the SQL:

UPDATE Tbl_Vendor SET Tbl_Vendor.AssociatedProject.[Value] = 3
WHERE (((Tbl_Vendor.AssociatedProject.Value) Is Null));

Any help is greatly appreciated!

 

[plog]

Tbl_Vendor.AssociatedProject.[Value]

Isn't syntatically correct. You only get to use one dot. Everything before the dot is the name of the table, everything after is the name of the field. When you use more than one dot, it can't parse it.

What are the fields of Tbl_Vendor?

 

[duthiedon]

Interesting, first it's generated by MS Access using their standard interface and I just showed the SQL from it, and it's also in the same format that MS Help has.

Here's all my fields in my table:

ID-Vendor > (Index and autonumber)
Vendor > text field
Created > date field
BusinessAddress > memo
BusinessNumber > text field
VendorNotes > memo
AssociatedProject > number and linked to another table

 

[plog]

This SQL should do what you want:

UPDATE Tbl_Vendor SET Tbl_Vendor.AssociatedProject = 3
WHERE (((Tbl_Vendor.AssociatedProject) Is Null));

I advise backing up your data before running it though.

 

[spikepl]

Which records do you wish to update. All? Or all but the 9 with values in the multivalued field?

 

[duthiedon]

Thanks plog, I ran the query and I get a message saying "An update or delete query cannot contain a multi-valued field"

Spikepl, I need to update them all, the 9 already have the value set, so it doesn't matter whether I update the remainder of the 9 or all of them, it will have the same outcome.

 

[spikepl]

UPDATE Tbl_Vendor SET Tbl_Vendor.AssociatedProject.[Value] = 3

 

[duthiedon]

Thanks spikepl, that's what I had tried earlier, and just did a copy and paste in case i made an error, and it only wants to update the 9 records that already have a value set.

 

[spikepl]

INSERT INTO Tbl_Vendor( Tbl_Vendor.AssociatedProject.[Value] ) VALUES (3)

 

[duthiedon]

Excellent! It works Thank you very much spikepl!!