Dear Microsoft Gurus,
I am teaching myself Microsoft Access 2007. So, please forgive me if this question is very naive.
I am learning how to use “Update Queries”. I have a database with a field called “specialties needed” . It is a multi-valued field with a lookup. In other words, it has a drop down menu of choices of values and allows choosing more than one value to be chosen. The values are text strings.
I created a query named “Trying to update null specialties needed field” .
The query does not work as I expected it to. Ideally, I wanted to find all the rows with empty “specialties needed” field and populate the “specialties needed” field in these rows with two values: “Phy” and “CNA” (so that what I see in that field is CNA, Phy) Note that these two values already occur on the list of the possile values for that field.
I tried several versions of the query. I keep getting error message: “An Update or Delete query cannot contain a multi-valued lookup field” .
Am I doing this wrong, or am I trying to do something that is truly impossible to do? If it is impossible to do this, then what does one do if one has a large database with a a multi-valued lookup field and one needs to make some “bulk change” to that field?
Here is the SQL for the versions of the query that I tried.
Version 1:
UPDATE Client_table SET Client_table.[Specialties needed] = "Phy" And "CNA"
WHERE (((Client_table.[Specialties needed].Value) Is Null));
Version 2:
UPDATE Client_table SET Client_table.[Specialties needed] = "Phy" And "CNA"
WHERE (((Client_table.[Specialties needed].Value) Is Null)) OR (((Client_table.[Specialties needed].Value)=""));
Version 3: (Version 3 gives up on the idea of populating that field with two values, and just tries to put in one value. ).
UPDATE Client_table SET Client_table.[Specialties needed] = "Phy"
WHERE (((Client_table.[Specialties needed].Value) Is Null)) OR (((Client_table.[Specialties needed].Value)=""));
Thank you very much!
Studiosa
Bookmarks