Trouble with concatenated string function

[Kurt]

I'm using the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field from
the Many table of a 1:M relationship into a concatenated string. The
function puts everything in a comma separated format. In my case, it
returns a list of authors:

Borman, W., Oppler, S., White, L.,

I'd like to change the function so the last record in the string is
preceded by an ampersand instead of a comma, as in:

Borman, W., Oppler, S., & White, L.

(Note: Each last name and initial is actually a concatenated field done
in a query, using: "Author: [LName] & ", " & [Initials]"). In other
words, some of those commas are mine.)

I've tried to edit the function to use the ampersand. Within the
loop, I've added code to keep track of what the current record is so
that once I've finished the loop, I will know the last record added
to the string (i.e., the last author). I then try to remove the entry,
add an ampersand, then add the last record back.

The change I made correctly added the ampersand and the last record,
but it didn't delete the last record. In other words, the last author
is repeated twice, as in:

Borman, W., Oppler, S., White, L., & White, L.

It appears that the code to remove the last record plus the trailing
"," is
either not doing anything, or is being killed:

'Remove the last record added plus the trailing ","
fConcatChild = Left(varConcat, Len(varCurrRecord) + 1)

I know this because I made major changes to this syntax and it had no
observable effect. The next line of code works fine:

'Add the ampersand and the last record
fConcatChild = varConcat & "& " & varCurrRecord

.... but when I bebug this with:

Debug.Print "varConcat: " & varConcat, "varCurrRecord: " &
varCurrRecord

.... I get

varConcat: Borman, W., Hanson, M., Oppler, S., & White, L.,
varCurrRecord: White, L.

.... which confirms that the last record is not being deleted.

Any ideas? I've tried everything and my brain is melting. Thanks.
(Full code below.)

Kurt
'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
Dim db As Database
Dim rs As Recordset
Dim varCurrRecord As Variant
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable &
"]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue &
"'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varCurrRecord = rs(strFldConcat)
varConcat = varConcat & varCurrRecord & ", "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Remove the last record added plus the trailing ","
fConcatChild = Left(varConcat, Len(varCurrRecord) + 1)
'Add the ampersand and the last record
fConcatChild = varConcat & "& " & varCurrRecord

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

'************ Code End **********

[Chuck Grimsby]

I use a function I wrote a few years back for doing this that I call
"TableAsString".

A quick hack of my "TableAsString" function produced this, which does
what you are asking for:

Public Function TableAsString( _
strTableName As String, _
strFieldName As String, _
strSeperator As String, _
Optional bolTextQualifiers As Boolean = True, _
Optional strOrderByField As String = "", _
Optional strWhere As String = "", _
Optional whatDB As DAO.Database = Nothing, _
Optional strLastSeperator As String = "") _
As String

Dim myDB As DAO.Database
Dim myRST As DAO.Recordset
Dim strSQL As String
Dim strReturnString As String
Dim bolISetTheDB As Boolean

If strLastSeperator = "" Then _
strLastSeperator = strSeperator

If whatDB Is Nothing Then
Set whatDB = CurrentDb
bolISetTheDB = True
End If

strSQL = "SELECT [" & strFieldName & "]" & _
"FROM [" & strTableName & "]"
If strWhere <> "" Then
strSQL = strSQL & " WHERE " & strWhere
End If
strSQL = strSQL & " GROUP BY [" & strFieldName & "]"
If strOrderByField <> "" Then
If strOrderByField <> strFieldName Then
strSQL = strSQL & ", [" & strOrderByField & "] "
End If
strSQL = strSQL & " ORDER BY [" & strOrderByField & "]"
End If
Set myRST = whatDB.OpenRecordset(strSQL, , dbOpenForwardOnly)
If Not myRST.EOF Then
With myRST
.MoveFirst
If bolTextQualifiers = True Then
strReturnString = Chr$(34) & _
myRST(strFieldName) & _
Chr$(34)
Else
strReturnString = Nz(myRST(strFieldName), _
"Null")
End If
Do
.MoveNext
If .EOF Then Exit Do
If myRST.AbsolutePosition + 1 = _
myRST.RecordCount Then
strReturnString = _
strReturnString & _
strLastSeperator
Else
strReturnString = _
strReturnString & _
strSeperator
End If
If bolTextQualifiers = True Then
strReturnString = _
strReturnString & _
Chr$(34) & _
myRST(strFieldName) & _
Chr$(34)
Else
strReturnString = _
strReturnString & _
myRST(strFieldName)
End If
Loop While Not .EOF
End With
End If
myRST.Close
Set myRST = Nothing

If bolISetTheDB = True Then
Set whatDB = Nothing
End If
TableAsString = strReturnString
End Function

On 14 Dec 2005 14:40:17 -0800, "Kurt" <kh*******@cox.net> wrote:

I'm using the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field from
the Many table of a 1:M relationship into a concatenated string. The
function puts everything in a comma separated format. In my case, it
returns a list of authors:
Borman, W., Oppler, S., White, L.,
I'd like to change the function so the last record in the string is
preceded by an ampersand instead of a comma, as in:
Borman, W., Oppler, S., & White, L.
(Note: Each last name and initial is actually a concatenated field done
in a query, using: "Author: [LName] & ", " & [Initials]"). In other
words, some of those commas are mine.)
I've tried to edit the function to use the ampersand. Within the
loop, I've added code to keep track of what the current record is so
that once I've finished the loop, I will know the last record added
to the string (i.e., the last author). I then try to remove the entry,
add an ampersand, then add the last record back.
The change I made correctly added the ampersand and the last record,
but it didn't delete the last record. In other words, the last author
is repeated twice, as in:
Borman, W., Oppler, S., White, L., & White, L.
It appears that the code to remove the last record plus the trailing
"," is
either not doing anything, or is being killed:
'Remove the last record added plus the trailing ","
fConcatChild = Left(varConcat, Len(varCurrRecord) + 1)
I know this because I made major changes to this syntax and it had no
observable effect. The next line of code works fine:
'Add the ampersand and the last record
fConcatChild = varConcat & "& " & varCurrRecord
... but when I bebug this with:
Debug.Print "varConcat: " & varConcat, "varCurrRecord: " &
varCurrRecord
... I get
varConcat: Borman, W., Hanson, M., Oppler, S., & White, L.,
varCurrRecord: White, L.
... which confirms that the last record is not being deleted.
Any ideas? I've tried everything and my brain is melting. Thanks.
(Full code below.)

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[rkc]

Kurt wrote:

I'm using the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field from
the Many table of a 1:M relationship into a concatenated string. The
function puts everything in a comma separated format. In my case, it
returns a list of authors:

Assuming you're using a post 97 version of Access and all your records
are unique, just do a

Replace(varConcat, "," & lastBlahBlah, "&" & lastBlahBlah)

or some such.

[Bob Quintal]

"Kurt" <kh*******@cox.net> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

I'm using the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a
field from the Many table of a 1:M relationship into a
concatenated string. The function puts everything in a comma
separated format. In my case, it returns a list of authors:

Borman, W., Oppler, S., White, L.,

I'd like to change the function so the last record in the
string is preceded by an ampersand instead of a comma, as in:

Borman, W., Oppler, S., & White, L.

(Note: Each last name and initial is actually a concatenated
field done in a query, using: "Author: [LName] & ", " &
[Initials]"). In other words, some of those commas are mine.)
'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varCurrRecord = rs(strFldConcat)
varConcat = varConcat & varCurrRecord & ", "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Remove the last record added plus the trailing ","
fConcatChild = Left(varConcat, Len(varCurrRecord) + 1)
'Add the ampersand and the last record
fConcatChild = varConcat & "& " & varCurrRecord

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

'************ Code End **********

calculate the shortened length ot the string, and take the left
() number of characters.
x = len(varConcat)
y = len(varCurrentRecord)
z = len(", ")
(I know that it looks dumb, but if you analyse your code against
the equation you'll find your problems.)

left(varConcat,x-(y+z)) + " & " varCurrentRecord

--
Bob Quintal

PA is y I've altered my email address.

[Kurt]

Thank you for all your suggestions. rkc's Replace suggestion got me in
the right direction the fastest, but I got a good, complete solution
from Doug Steele, MS Access MVP. The code now also handles cases with a
single author. Final relevant code for those interested:

With rs
If .RecordCount > 1 Then
'start concatenating records
Do While Not rs.EOF
varCurrRecord = rs(strFldConcat)
varConcat = varConcat & varCurrRecord & ", "
.MoveNext
Loop
'Remove the last record, plus the trailing ","
varConcat = Left(varConcat, Len(varConcat) -
Len(varCurrRecord) - 2)
'Add the ampersand and the last record
fConcatChild = varConcat & "& " & varCurrRecord
ElseIf .RecordCount = 1 Then
Do While Not rs.EOF
varCurrRecord = rs(strFldConcat)
varConcat = varConcat & varCurrRecord
.MoveNext
Loop
fConcatChild = varConcat
End If
End With