RE: Trouble with concatenate function

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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!


"Kurt" wrote:

> 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 **********
>
>
.

Relevant Pages

  • RE: Multi select List box to filter query for editing
    ... Dim mFilter As String ... ' Complete string for filter to apply to query ...
    (microsoft.public.access.formscoding)
  • Programatically Changing Query Criteria
    ... Save your query in SQL. ... the following to determine your criteria. ... Dim strSQL As String ... MsgBox "There was a problem building the SQL String" ...
    (microsoft.public.access.macros)
  • Re: Selecting certain items for a report
    ... I created the query called tmpSelectProducts ... > highlights the Dim qdf as DAO.QueryDef line. ... > Dim StrWhere As String ... > End Sub ...
    (microsoft.public.access.forms)
  • RE: Recordset looping (and debug looping!)
    ... create a new query using the following SQL: ... I named it "Sndx" with a datatype of String. ... Dim rst As DAO.Recordset, strNames As String ... ' good name - add soundex code and save record ...
    (microsoft.public.access.formscoding)
  • Re: Dynamic Query, Using ListBox and VBA
    ... SELECT DISTINCT tblCompanies.strCompanyCountries FROM tblCompanies UNION ... Using a Microsoft Access Listbox to pass criteria to a query ... Dim MyDB As DAO.Database ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)