473,246 Members | 1,461 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

Getting Values from Multi-Value Fields

124 100+
Is there some easy way to "flatten" the multiple values from a multi-value field? I should state at the outset that I hate multi-value fields, but unfortunately they were used and I need to retrieve the multiple values from a field then concatenate them into one string. Currently I am doing something like this, but it seems to take all day.

Expand|Select|Wrap|Line Numbers
  1.  strField = "MediaReturnedReason"
  2.     strMediaReturnedReason = vbNullString
  3.     strMediaTypeInSQL = "Select " & strField & ".Value AS Val From tblMain Where MainID = " & RS!MainID
  4.     Set MultiRS = DB.OpenRecordset(strMediaTypeInSQL)
  5.     Set valFLD = MultiRS.Fields("Val")
  6.     intCount = DCount(strField, "tblMain", "MainID =" & RS!MainID)
  7.     If intCount > 1 Then
  8.         MultiRS.MoveFirst
  9.         Do Until MultiRS.EOF
  10.             strMediaReturnedReason = strMediaReturnedReason & ", " & valFLD
  11.             MultiRS.MoveNext
  12.         Loop
  13.     Else
  14.         strMediaReturnedReason = Nz(valFLD, vbNullString)
  15.     End If
  16.  
  17.     If Left(strMediaReturnedReason, 2) = ", " Then
  18.         strMediaReturnedReason = Right(strMediaReturnedReason, Len(strMediaReturnedReason) - 2)
  19.     End If
There must be a faster way of doing this. Thank you a gigabyte in advance for any guidance.
Apr 16 '18 #1
6 4746
PhilOfWalton
1,430 Expert 1GB
Perhaps if you provided some samples of the input values, and a little information on the source of these values and the required output and what you want to do with that output, someone may be able to help.

Phil
Apr 17 '18 #2
BikeToWork
124 100+
The input values are multiple values come from a single attachment field. These will be none, one or multiple values - 'production', 'qc', 'requested' and 'return to sender'. What I'm trying to do is combine the multiple values into a comma separated string so that if the values selected were 'production' and 'qc' for the field, the string would be 'production, qc'. I am trying to flatten out the multiple values into one string for reporting purposes.
Apr 17 '18 #3
twinnyfo
3,653 Expert Mod 2GB
BTW,

Could you please clarify: you state that this is a multi-value field in Post #1, but then in Post #3, you say that it is a "single attachment field".

Depending upon the type of field will guide how we troubleshoot.

Also, I share your hatred of multi-value fields, as I can find no inherent value in them.

Although, I think you are on the right track with your current code (which clearly indicates a MV list, not an attachment). THere might be a few ways making it slightly more efficient, but the concept would remain the same: cycle through each value in the MV list, concatenate them together.
Apr 18 '18 #4
twinnyfo
3,653 Expert Mod 2GB
Here is a shot at flattening the field in the entire table. I've added a field Flatten into my table to receive the concatenated string. The rest should be pretty self-explanatory.

Expand|Select|Wrap|Line Numbers
  1. Public Sub Flatten()
  2. On Error GoTo EH
  3.     Dim strSQL  As String
  4.     Dim db      As DAO.Database
  5.     Dim rstID   As DAO.Recordset
  6.     Dim rstVal  As DAO.Recordset
  7.     Dim strFlat As String
  8.  
  9.     strSQL = "SELECT ID, Flatten " & _
  10.         "FROM tblMVList;"
  11.     Set db = CurrentDb()
  12.     Set rstID = db.OpenRecordset(strSQL)
  13.     With rstID
  14.         If Not .RecordCount = 0 Then
  15.             .MoveFirst
  16.             Do While Not .EOF
  17.                 strFlat = ""
  18.                 strSQL = "SELECT MVField.Value AS MVValue " & _
  19.                     "FROM tblMVList " & _
  20.                     "WHERE ID = " & !ID & ";"
  21.                 Set rstVal = db.OpenRecordset(strSQL)
  22.                 With rstVal
  23.                     If Not .RecordCount = 0 Then
  24.                         Do While Not .EOF
  25.                             If strFlat = "" Then
  26.                                 strFlat = !MVValue
  27.                             Else
  28.                                 strFlat = strFlat & ", " & !MVValue
  29.                             End If
  30.                             .MoveNext
  31.                         Loop
  32.                     End If
  33.                     .Close
  34.                 End With
  35.                 Set rstVal = Nothing
  36.                 .Edit
  37.                 !Flatten = strFlat
  38.                 .Update
  39.                 .MoveNext
  40.             Loop
  41.         End If
  42.         .Close
  43.     End With
  44.     db.Close
  45.     Set rstID = Nothing
  46.     Set db = Nothing
  47.  
  48.     Exit Sub
  49. EH:
  50.     MsgBox "There was an error flattening the field!  " & vbCrLf & vbCrLf & _
  51.         Err.Number & vbCrLf & _
  52.         Err.Description & vbCrLf & vbCrLf & _
  53.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  54.     Exit Sub
  55. End Sub
Hope this hepps!
Apr 18 '18 #5
BikeToWork
124 100+
Twinnyfo, thanks for the response. The fields I am attempting to flatten out are multi-value fields where multiple values are stored in a single field (through some hocus-pocus that Access does behind the scene). I'll try your code. Thanks a lot.
Apr 18 '18 #6
PhilOfWalton
1,430 Expert 1GB
I presume there is a good reason why you can't get rid of the multi-value field and use a straightforward table of values and modify the TblMain to have an pointer to your new table.

Phil
Apr 18 '18 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: spiderman | last post by:
Hi, How can I getting parameter values from more than 1 HTML form onto the same ASP page? Thank you
3
by: Jen | last post by:
Hi I have created some controls (HtmlInputText, HtmlGenericControl, TextBox) dynamically. But I have problem getting the values from these controls to save them. Is there a way to do that? ...
1
by: MattB | last post by:
OK, never mind my last post. It was easy enough to refer to the table the repeater is bound to, but I made a big, incorrect assumption in that post. In my last post I thought I was successfully...
2
by: Kiran Kumar Pinjala | last post by:
Hi, May be this is silly, or I just need a second pair eyes to look at this. I am trying to get values that I have edited in a datagrid and update the values with those values. Here is the code...
3
by: Michael Glass | last post by:
I'm working on an ASP.Net web app using VS2005 and the .Net 2.0 framework, and I have a serious problem with the page I'm currently working on. The page has, among other things, two FormViews and a...
9
by: Good Man | last post by:
Hi This is sort of a weird question, perhaps a bit off-topic... I am on the 'edit' screen of a web form, and I have a bunch of variables coming from a database that need to be placed into the...
10
by: psbasha | last post by:
Hi, Dictionary works in getting values from the key values.But I would like to get keys from values. For example : d = {100: ,102: ,202: } d.get(102)
0
by: R Bandeira | last post by:
How to keep values in fields after ChangeMode to Insert from Edit? I am implementing a re-insert type feature, after an edit. // Change form to insert mode...
9
Catalyst159
by: Catalyst159 | last post by:
I have a form which is used to calculate residential Floor Area Ratio (FAR). The form is structured into seven parts as follows: Part A: Maximum FAR and Floor Area: Part B: Gross Floor Area of...
11
by: hvsummer | last post by:
] Hi everyone, This is my first topic, clap clap clap when a lot of people think that Multivalue Field will break database normalization rule, that's not correct. first thing, to understand...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.