Update frickin-frackin multi-value field

[snow-raven]

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:

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:

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!

 

[isladogs]

Umm... two things
1. Have a look at my article about MVFs - http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763
Queries on MVFs are possible but can be very difficult to get right

2. If you are going to listen to Allen Browne then why are you going to use 8 Yes/No fields against his advice?

 

[snow-raven]

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.

 

[Deleted member 147267]

Agree with dumping the mvf, but maybe I can offer some insight? From reading the sql, it appears that [Value] could be interpreted as a field because of the square brackets, so your sample isn't quite the same as step 5 of Update query at https://support.office.com/en-us/ar...ries-6f64f92d-659f-411c-9503-b6624e1e323a#bm8

I think I can say "never" when it comes to using [ ] around properties or methods.

 

[snow-raven]

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;

UPDATE Sample_Intervals SET Sample_Intervals.Vis_Lab_Tests.Value = 3
WHERE ((Sample_Intervals.Vis_Lab_Tests.Value) Is Null);

 

[theDBguy]

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]

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: