468,539 Members | 1,618 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

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 3186
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,650 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,650 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

Post your reply

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

Similar topics

2 posts views Thread by Kiran Kumar Pinjala | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.