Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-01-2019, 02:07 PM   #1
snow-raven
Newly Registered User
Gold Supporter
 
Join Date: Apr 2018
Posts: 32
Thanks: 19
Thanked 2 Times in 1 Post
snow-raven is on a distinguished road
Update frickin-frackin multi-value field

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:
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 is offline   Reply With Quote
Old 02-01-2019, 02:11 PM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,443
Thanks: 106
Thanked 2,533 Times in 2,326 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Update frickin-frackin multi-value field

Umm... two things
1. Have a look at my article about MVFs - http://www.mendipdatasystems.co.uk/m...lds/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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
snow-raven (02-01-2019)
Old 02-01-2019, 03:46 PM   #3
snow-raven
Newly Registered User
Gold Supporter
 
Join Date: Apr 2018
Posts: 32
Thanks: 19
Thanked 2 Times in 1 Post
snow-raven is on a distinguished road
Re: Update frickin-frackin multi-value field

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.

snow-raven is offline   Reply With Quote
Old 02-01-2019, 03:57 PM   #4
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 722
Thanks: 3
Thanked 148 Times in 142 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Update frickin-frackin multi-value field

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/art...624e1e323a#bm8

I think I can say "never" when it comes to using [ ] around properties or methods.
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
snow-raven (02-01-2019)
Old 02-01-2019, 04:17 PM   #5
snow-raven
Newly Registered User
Gold Supporter
 
Join Date: Apr 2018
Posts: 32
Thanks: 19
Thanked 2 Times in 1 Post
snow-raven is on a distinguished road
Re: Update frickin-frackin multi-value field

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);
snow-raven is offline   Reply With Quote
Old 02-01-2019, 04:24 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,048
Thanks: 36
Thanked 724 Times in 707 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Update frickin-frackin multi-value field

Hi. Moving a MVF can be done with both VBA and SQL. I don't remember which one I used in this demo.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 02-01-2019, 04:41 PM   #7
snow-raven
Newly Registered User
Gold Supporter
 
Join Date: Apr 2018
Posts: 32
Thanks: 19
Thanked 2 Times in 1 Post
snow-raven is on a distinguished road
Re: Update frickin-frackin multi-value field

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:


snow-raven is offline   Reply With Quote
Reply

Tags
multi-value field , sql , update query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Update Query with multi-value field g28dman General 3 09-12-2015 07:06 AM
Update multi records xaysana Queries 1 10-27-2011 08:29 PM
Auto-populating a multivalue field by another multi-value field checks MyWebdots Forms 1 02-23-2011 02:04 AM
Multi Update By Checkbox nsquared Forms 1 02-09-2010 06:23 PM
Multi Update NigelShaw General 5 05-24-2008 11:02 AM




All times are GMT -8. The time now is 04:13 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World