[AccessD] Mailing Labels

...another AD special for my code library :)

William

----- Original Message ----- 
From: "A.D.TEJPAL" <adtp at airtelbroadband.in>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Wednesday, June 21, 2006 3:03 PM
Subject: Re: [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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com