Update frickin-frackin multi-value field (1 Viewer)

snow-raven

Registered User.
Local time
Today, 14:00
Joined
Apr 12, 2018
Messages
48
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/article/using-multivalued-fields-in-queries-6f64f92d-659f-411c-9503-b6624e1e323a#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:
Code:
SELECT Sample_Intervals.Vis_Lab_Testing, Sample_Intervals.Vis_Lab_Tests.Value
FROM Sample_Intervals
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:
Code:
UPDATE Sample_Intervals 
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
Thanks!
 

snow-raven

Registered User.
Local time
Today, 14:00
Joined
Apr 12, 2018
Messages
48
I'm working right now on making his fake yes/no fields. I haven't ever set a programmatic property like that before, so I'm still figuring out the checkbox.

Based on your page (thank you, so helpful!), I think my mistake was in assuming that the values would be 1, 2, 3... etc. That explains another thing I hadn't figured out, which is why some records had "3" more than once. I guess I'll need to do some experimentation to see what they really are.
 

Micron

AWF VIP
Local time
Today, 17:00
Joined
Oct 20, 2018
Messages
3,476

snow-raven

Registered User.
Local time
Today, 14:00
Joined
Apr 12, 2018
Messages
48
Thanks, Micron. I actually had the same thought, since I was using step 5 as my reference, but Access wraps the brackets when I build the query in visual mode. I messed with the SQL after that- with, without, without the 'Is Null', removing extra parenthesis that Access created, with & without the quotes Access put on the mv field's parameter, standing on my head with one eye closed; but I guess I didn't post that earlier version, sorry.

I don't even get an Update result from the 'Is Null' which works fine in the Select query;
Code:
UPDATE Sample_Intervals SET Sample_Intervals.Vis_Lab_Tests.Value = 3
WHERE ((Sample_Intervals.Vis_Lab_Tests.Value) Is Null);
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:00
Joined
Oct 29, 2018
Messages
21,358
Hi. Moving a MVF can be done with both VBA and SQL. I don't remember which one I used in this demo.
 

snow-raven

Registered User.
Local time
Today, 14:00
Joined
Apr 12, 2018
Messages
48
Sadly, I had to go to a 64-bit installation for the Geotech software I'm interfacing to work, so my computer won't run your code unless I figure out how to modify it.

I'm not doubting that it can be done, I'm just at a bit of a loss for what I'm doing wrong. Also not sure about the exports I got out of the original database now. They are sequential from 3127 to 3648, which doesn't make sense or correlate at all with the contents of the original mv field.

Should have listened to the warnings when I built it!
I seem to do this a lot here: :banghead:
 

Users who are viewing this thread

Top Bottom