Trouble with concatenate function

[Guest]

I’m using a the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field with several
values from the Many table of a 1:M relationship in a comma separated format.

I’m using the function in a query and then combining it with another query
to create a list of references like:

Borman, W., Hanson, M., Oppler, S., & White, L. (1993). Role of early
supervisory experience in supervisor performance. Journal of Applied
Psychology, 78, 443-449.

But the function doesn’t add the “&†symbol to the last item like the above
example. Instead, the field returned looks like: “… Oppler, S., White L.,â€

Is there any way to edit the function so that the last item can be joined
with an “&†and not have a trailing comma? I’m trying to match a certain
citation style (APA).

Thanks. (Function 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 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
varConcat = varConcat & rs(strFldConcat) & ", "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

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

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

 

[Guest]

Unsure exactly what presentation purposes you're going for, but couldn't you
try creating a new query (in design view). Loading all the tables you wish to
add fields from. ( presuming they are connected with a relationship)

and then manually type [Field1]&[Field2]&[Field3] ... etc?

Unsure of how to remove the comma if it is actually stored within one of the
fields, my tutor is planned to teach me/help me write some code that will
store aconcatenated field, each value seperated by commas, back into multiple
fields. So if I learn anything that may be of use, will post it to you.

Hope my "lame" excuse for a suggestion might have helped you, you're
probably talking way over my head

Dan

--
iPod's ROCK!

I’m using a the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field with several
values from the Many table of a 1:M relationship in a comma separated format.

I’m using the function in a query and then combining it with another query
to create a list of references like:

Borman, W., Hanson, M., Oppler, S., & White, L. (1993). Role of early
supervisory experience in supervisor performance. Journal of Applied
Psychology, 78, 443-449.

But the function doesn’t add the “&†symbol to the last item like the above
example. Instead, the field returned looks like: “… Oppler, S., White L.,â€

Is there any way to edit the function so that the last item can be joined
with an “&†and not have a trailing comma? I’m trying to match a certain
citation style (APA).

Thanks. (Function 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 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
varConcat = varConcat & rs(strFldConcat) & ", "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

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

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

 

[Douglas J. Steele]

Within the loop, you'll need to keep track of what the current record is so
that once you've finished the loop, you'll know the last record added. You'd
then remove that entry, add an ampersand then add the last record back.

Something like:

'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

Note that this won't work if you only have a single record you're adding:
you'll have to check for that case and only remove the final ,, rather than
putting in the ampersand.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm using a the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field with
several
values from the Many table of a 1:M relationship in a comma separated
format.

I'm using the function in a query and then combining it with another query
to create a list of references like:

Borman, W., Hanson, M., Oppler, S., & White, L. (1993). Role of early
supervisory experience in supervisor performance. Journal of Applied
Psychology, 78, 443-449.

But the function doesn't add the "&" symbol to the last item like the
above
example. Instead, the field returned looks like: ". Oppler, S., White L.,"

Is there any way to edit the function so that the last item can be joined
with an "&" and not have a trailing comma? I'm trying to match a certain
citation style (APA).

Thanks. (Function 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 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
varConcat = varConcat & rs(strFldConcat) & ", "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

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

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

 

[Guest]

This correctly added the ampersan 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., Hanson, M., Oppler, S., White, L., & White, L. (1993). ...

I assume the culprit is with this line, but various changes I tried didn't
fix the issue:

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

Any ideas? Thanks.

Kurt

Within the loop, you'll need to keep track of what the current record is so
that once you've finished the loop, you'll know the last record added. You'd
then remove that entry, add an ampersand then add the last record back.

Something like:

'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

Note that this won't work if you only have a single record you're adding:
you'll have to check for that case and only remove the final ,, rather than
putting in the ampersand.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm using a the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field with
several
values from the Many table of a 1:M relationship in a comma separated
format.

I'm using the function in a query and then combining it with another query
to create a list of references like:

Borman, W., Hanson, M., Oppler, S., & White, L. (1993). Role of early
supervisory experience in supervisor performance. Journal of Applied
Psychology, 78, 443-449.

But the function doesn't add the "&" symbol to the last item like the
above
example. Instead, the field returned looks like: ". Oppler, S., White L.,"

Is there any way to edit the function so that the last item can be joined
with an "&" and not have a trailing comma? I'm trying to match a certain
citation style (APA).

Thanks. (Function 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 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
varConcat = varConcat & rs(strFldConcat) & ", "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

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

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

 

[Guest]

I’ve tried so many different changes to the module, including different ways
to parse out the string and so forth, but nothing is fixing the problem.

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

'That's it... you should have a concatenated string now
'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.

'Add the ampersand and the last record
fConcatChild = varConcat & "& " & varCurrRecord
Debug.Print "varConcat: " & varConcat, "varCurrRecord: " & varCurrRecord

When I debugged the next line (as above), it produced this:

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

So, again: It appears that the code designed to remove the last record is
not getting executed.

Any ideas? I think I’ve tried everything.

Kurt

Within the loop, you'll need to keep track of what the current record is so
that once you've finished the loop, you'll know the last record added. You'd
then remove that entry, add an ampersand then add the last record back.

Something like:

'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

Note that this won't work if you only have a single record you're adding:
you'll have to check for that case and only remove the final ,, rather than
putting in the ampersand.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm using a the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field with
several
values from the Many table of a 1:M relationship in a comma separated
format.

I'm using the function in a query and then combining it with another query
to create a list of references like:

Borman, W., Hanson, M., Oppler, S., & White, L. (1993). Role of early
supervisory experience in supervisor performance. Journal of Applied
Psychology, 78, 443-449.

But the function doesn't add the "&" symbol to the last item like the
above
example. Instead, the field returned looks like: ". Oppler, S., White L.,"

Is there any way to edit the function so that the last item can be joined
with an "&" and not have a trailing comma? I'm trying to match a certain
citation style (APA).

Thanks. (Function 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 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
varConcat = varConcat & rs(strFldConcat) & ", "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

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

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

 

[Douglas J. Steele]

Sorry, you're correct that I put the wrong expression.

It should have been

fConcatChild = Left(varConcat, Len(varCurrRecord) + 1)
'Add the ampersand and the last record
fConcatChild = varConcat & " & " & varCurrRecord


varConcat = Left(varConcat, Len(varConcat) - Len(varCurrRecord) - 1)
'Add the ampersand and the last record
fConcatChild = varConcat & " & " & varCurrRecord

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've tried so many different changes to the module, including different
ways
to parse out the string and so forth, but nothing is fixing the problem.

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

'That's it... you should have a concatenated string now
'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.

'Add the ampersand and the last record
fConcatChild = varConcat & "& " & varCurrRecord
Debug.Print "varConcat: " & varConcat, "varCurrRecord: " &
varCurrRecord

When I debugged the next line (as above), it produced this:

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

So, again: It appears that the code designed to remove the last record is
not getting executed.

Any ideas? I think I've tried everything.

Kurt

Within the loop, you'll need to keep track of what the current record is
so
that once you've finished the loop, you'll know the last record added.
You'd
then remove that entry, add an ampersand then add the last record back.

Something like:

'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

Note that this won't work if you only have a single record you're adding:
you'll have to check for that case and only remove the final ,, rather
than
putting in the ampersand.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm using a the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field with
several
values from the Many table of a 1:M relationship in a comma separated
format.

I'm using the function in a query and then combining it with another
query
to create a list of references like:

Borman, W., Hanson, M., Oppler, S., & White, L. (1993). Role of
early
supervisory experience in supervisor performance. Journal of Applied
Psychology, 78, 443-449.

But the function doesn't add the "&" symbol to the last item like the
above
example. Instead, the field returned looks like: ". Oppler, S., White
L.,"

Is there any way to edit the function so that the last item can be
joined
with an "&" and not have a trailing comma? I'm trying to match a
certain
citation style (APA).

Thanks. (Function 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 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
varConcat = varConcat & rs(strFldConcat) & ", "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

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

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

 

[Guest]

Excellent. Made a slight change and everything's working. Also handled cases
with a single author. Thanks so much for your help. Final code:

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

Sorry, you're correct that I put the wrong expression.

It should have been

fConcatChild = Left(varConcat, Len(varCurrRecord) + 1)
'Add the ampersand and the last record
fConcatChild = varConcat & " & " & varCurrRecord


varConcat = Left(varConcat, Len(varConcat) - Len(varCurrRecord) - 1)
'Add the ampersand and the last record
fConcatChild = varConcat & " & " & varCurrRecord

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've tried so many different changes to the module, including different
ways
to parse out the string and so forth, but nothing is fixing the problem.

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

'That's it... you should have a concatenated string now
'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.

'Add the ampersand and the last record
fConcatChild = varConcat & "& " & varCurrRecord
Debug.Print "varConcat: " & varConcat, "varCurrRecord: " &
varCurrRecord

When I debugged the next line (as above), it produced this:

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

So, again: It appears that the code designed to remove the last record is
not getting executed.

Any ideas? I think I've tried everything.

Kurt

Within the loop, you'll need to keep track of what the current record is
so
that once you've finished the loop, you'll know the last record added.
You'd
then remove that entry, add an ampersand then add the last record back.

Something like:

'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

Note that this won't work if you only have a single record you're adding:
you'll have to check for that case and only remove the final ,, rather
than
putting in the ampersand.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I'm using a the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field with
several
values from the Many table of a 1:M relationship in a comma separated
format.

I'm using the function in a query and then combining it with another
query
to create a list of references like:

Borman, W., Hanson, M., Oppler, S., & White, L. (1993). Role of
early
supervisory experience in supervisor performance. Journal of Applied
Psychology, 78, 443-449.

But the function doesn't add the "&" symbol to the last item like the
above
example. Instead, the field returned looks like: ". Oppler, S., White
L.,"

Is there any way to edit the function so that the last item can be
joined
with an "&" and not have a trailing comma? I'm trying to match a
certain
citation style (APA).

Thanks. (Function 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 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
varConcat = varConcat & rs(strFldConcat) & ", "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

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

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