423,849 Members | 1,876 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,849 IT Pros & Developers. It's quick & easy.

Getting Values from Multi-Value Fields

100+
P: 124
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
Share this Question
Share on Google+
6 Replies


PhilOfWalton
Expert 100+
P: 1,376
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

100+
P: 124
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
Expert Mod 2.5K+
P: 2,704
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
Expert Mod 2.5K+
P: 2,704
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

100+
P: 124
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
Expert 100+
P: 1,376
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.