[AccessD] Mailing Labels

Martin,

    Sample query given below should get you the desired label strings for each distinct four digit identifier prefix. It makes use of function Fn_GetLabel() given below.

    DataString is the name of field holding comma separated raw data in table T_Data.

Best wishes,
A.D.Tejpal
---------------

Select Query
=====================================
SELECT Left([DataString],4) AS PreFix, Fn_GetLabel(Left([DataString],4)) AS LabelString  FROM T_Data  
GROUP BY Left([DataString],4);
=====================================

User Defined Function
=====================================
Function Fn_GetLabel(ByVal IdString As String) As String
    On Error Resume Next
    Dim rst As DAO.Recordset
    Dim Txt As String, Qst As String, Rtv As Variant
    
    Txt = ""        ' Default
    Qst = "SELECT * FROM T_Data " & _
                "Where Left(DataString, 4) = '" & _
                IdString & "';"
    Set rst = CurrentDb.OpenRecordset(Qst)
    
    Do While Not rst.EOF
        Rtv = Split(rst.Fields("DataString"), ",")
        Txt = Txt & IIf(Len(Txt) > 0, ",", "") & Rtv(1)
        rst.MoveNext
    Loop
    
    Txt = IdString & "," & Txt & "," & Rtv(2)
    Fn_GetLabel = Txt
    
    rst.Close
    Set rst = Nothing
    On Error GoTo 0
End Function
=====================================

  ----- Original Message ----- 
  From: Martin Reid 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, June 21, 2006 19:40
  Subject: [AccessD] Mailing Labels


  Given the following data
   
  1234,Name,Address
  1234,Name,Address
  1234,Name,Address
  1234,Name,Address
  2345, Name Address
  2345,Name Address
   
  I need to get one mailing label produced 
   
  1234, Name1, Name2,Name3,Name4
  Address
   
  2345,Name1,Name2
  Address
   
  Etc
   
  So I can have multiple people at the same address. I need the name line to include all the names but only produce 1 label per address.
   
  Martin
   
   
  Martin WP Reid
  Training and Assessment Unit
  Riddle Hall
  Belfast
   
  tel: 02890 974477