Okay, I know all of the reasons one should not use a multi-value field, and I'm ready to admit defeat. However, I can't for the life of me figure out why this didn't work:
I'm working on Update queries for merging old database records into my new format. I have one field in the whole dang project that seemed to make the most sense as a multi-value field. When I wrote my Append query for that table, I got a message that said multi-value fields couldn't be included in an Append query, so I left that out and went on to figure out an Update query that would transfer the information.
I've read a bunch of different info about this, and tried duplicating this: https://support.office.com/en-us/art...624e1e323a#bm7
I even went so far as to manually copy the information from the imported temporary table into the target table and I STILL can't get it to work. Select query, no problem. Update query, no dice.
Background: I exported the previous version table from my original database as an Excel sheet and then imported it into the new template. For other data, I've been writing update queries & then deleting the "temporary" imported tables. The original data all imported as text, so the multi-value field shows as a string of numbers related to the primary key value of the original multi-value field; e.g. 3148.
This Select query works:
SELECT Sample_Intervals.Vis_Lab_Testing, Sample_Intervals.Vis_Lab_Tests.Value
WHERE (((Sample_Intervals.Vis_Lab_Testing) Like "*3*") AND ((Sample_Intervals.Vis_Lab_Tests.Value) Is Null));
When I change it to an Update query nearly identical to Microsoft's example, I get an empty datasheet:
SET Sample_Intervals.Vis_Lab_Tests.[Value] = 3
WHERE (((Sample_Intervals.Vis_Lab_Testing) Like "*3*")
AND ((Sample_Intervals.Vis_Lab_Tests.Value) Is Null));
(Note; I also tried this without the 'Is Null', but I wanted to make it as much like Microsoft's example as possible).
I'm giving up now, but this is driving me crazy. I'm going to convert my multi-value field into 8 yes/no fields. (And I'm going to be smart & listen to Allen Browne this time: http://allenbrowne.com/NoYesNo.html