[AccessD] Multi-value field format

I suppose you know all the arguments against multi-value fields by now, so I won't repeat 
them.    Here'a a simple concatentation function which uses a link table which stores the 
many-to-may relationships between products and categories 

Function ConcatCategories(Product_PK As Long) As String
Dim rs As DAO.Recordset
Dim strCategories as String

Set rs = DBEngine(0)(0).OpenRecordset( _
   "SELECT Category_Name FROM tblProductCategories " & _
   "INNER JOIN tblCategories ON " & _
   "tblCategories.Category_PK = tblProductCategories.Category_FK " & _
   "WHERE Product_FK = " & Product_PK)

While Not rs.EOF
   strCategories = strCategories & "," & rs!Category_Name
   rs.MoveNext
Wend

rs.Close

'If we got at least 1 category, strip the leading comma
If Len(strCategories) > 1 Then
    strCategories = Mid$(strCategories, 2)
End If

ConcatCategories = strCategories
End Function

On 11 May 2012 at 13:23, Susan Harkins wrote:

> A reader is considering the multi-value field for the following reason: 
> 
> "I have to format the data in this way as a last step to export to the
> e-commerce platform.  As I import a CSV table into the platform, it
> requires stuff like product name, sku, price, weight, etc.  It also
> has a field for category.  A product can belong to multiple
> categories, and the way the it imports/exports is to fill that field
> with the category name, separated by semicolons, in the one field.  A
> product can be a member of up to 20 categories (all separated by
> semicolons)." 
> 
> I'd like to steer him away from the multi-value field. I know
> combining field values into a single line is a common request but I've
> never had to do it myself -- any suggestions? 
> 
> Thanks!
> Susan H. 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>