Combining text Strings

[Steve]

Hi All,

I'm looking for a way to combine text strings on the many side of a one to
many relationship into one Concatenated field.
E.g. I have a property defined by a Property_Key on the one side and
Property Zone code on the many side and for a specific report I would like to
make a table/query with the Property Key with any Zoning Codes applicable to
that Property Key in a single field displaying like Z1 , Z2 , Z3...etc
instead of a table with duplicate Property keys for each Zone Code.

 

[NevilleT]

Hi Steve
The solution is to run a query and loop through the fields for each property
code. You then can loop through the fields for the zone and add them to a
string. In the example, I print them but you can do anything you want with
them.

Public Sub subCreateZone()
Dim dbs As Database
Dim rstProperty As Recordset
Dim rstZone As Recordset
Dim strPropertyZone As String

Dim strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT PropertyNo, PropertyName FROM tblPropertyt"
Set rstProperty = dbs.OpenRecordset(strSQL)

rstProperty.MoveFirst

Do While Not rstProperty.EOF
strPropertyZone = rstProperty!PropertyName
strSQL = "SELECT PropertyNo, ZoneName FROM tblZoneWHERE PropertyNo = " &
rstProperty!PropertyNo
Set rstZone = dbs.OpenRecordset(strSQL)

If rstZone.RecordCount > 0 Then
rstZone.MoveFirst
End If

Do While Not rstZone.EOF
strPropertyZone = strPropertyZone & ", " & rstZone!ZoneName
rstZone.MoveNext
Loop

Debug.Print strPropertyZone
rstProperty.MoveNext
Loop


End Sub

Neville Turbit
www.projectperfect.com.au