John, thanks sooooooo much--I'd not put in the fourth argument. Connie
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Connie-
>
> It works for me. In NorthWind 2007, I created this query:
>
> qryOrderProducts:
> SELECT [Order Details].[Order ID], Products.[Product Name]
> FROM Products INNER JOIN [Order Details] ON Products.ID = [Order
> Details].[Product ID];
>
> Then I created a query to call the function:
>
> SELECT Orders.[Order ID], Orders.[Order Date], ConcatRelated("[Product
> Name]","qryOrderProducts","[Order ID] = " & [Order ID],""," And ") AS Products
> FROM Orders;
>
> It returns the product name separated by " And ". Note that the query builder
> insisted that I put some argument in the fourth position. It wouldn't let me
> put just an extra comma.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Monday, May 23, 2011 9:32 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Browne's ConcatRelated and changing separator
>
> Hi all,
>
> Allen Browne's ConcatRelated code says that the fifth argument can be used to
> substitute a different separator for the ", " that is normally inserted. I have
> tried to use the fifth argument and it just puts in the default ", ".
>
> I am using the function in a query to concatenate the first names.
>
> Sellers: ConcatRelated("FirstName","qContactsSellerandSellerSpLN1","ListID = " &
> [ListingContacts].[ListID],," and ")
>
>
> An example of the result is : Chris, Heidi. I want Chris and Heidi or ideally
> Chris & Heidi.
>
> I've put his code below my name.
>
> Thanks!
> Connie
>
> Public Function ConcatRelated(strField As String, _
> strTable As String, _
> Optional strWhere As String, _
> Optional strOrderBy As String, _
> Optional strSeparator = ", ") As Variant
> On Error GoTo Err_Handler
> 'Purpose: Generate a concatenated string of related records.
> 'Return: String variant, or Null if no matches.
> 'Arguments: strField = name of field to get results from and concatenate.
> ' strTable = name of a table or query.
> ' strWhere = WHERE clause to choose the right values.
> ' strOrderBy = ORDER BY clause, for sorting the values.
> ' strSeparator = characters to use between the concatenated
> values.
> 'Notes: 1. Use square brackets around field/table names with spaces or
> odd characters.
> ' 2. strField can be a Multi-valued field (A2007 and later), but
> strOrderBy cannot.
> ' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as
> ZLSs.
> ' 4. Returning more than 255 characters to a recordset triggers
> this Access bug:
> ' http://allenbrowne.com/bug-16.html
> Dim rs As DAO.Recordset 'Related records
> Dim rsMV As DAO.Recordset 'Multi-valued field recordset
> Dim strSql As String 'SQL statement
> Dim strOut As String 'Output string to concatenate to.
> Dim lngLen As Long 'Length of string.
> Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field.
>
> 'Initialize to Null
> ConcatRelated = Null
>
> 'Build SQL string, and get the records.
> strSql = "SELECT " & strField & " FROM " & strTable
> If strWhere <> vbNullString Then
> strSql = strSql & " WHERE " & strWhere
> End If
> If strOrderBy <> vbNullString Then
> strSql = strSql & " ORDER BY " & strOrderBy
> End If
> Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
> 'Determine if the requested field is multi-valued (Type is above 100.)
> bIsMultiValue = (rs(0).Type > 100)
>
> 'Loop through the matching records
> Do While Not rs.EOF
> If bIsMultiValue Then
> 'For multi-valued field, loop through the values
> Set rsMV = rs(0).Value
> Do While Not rsMV.EOF
> If Not IsNull(rsMV(0)) Then
> strOut = strOut & rsMV(0) & strSeparator
> End If
> rsMV.MoveNext
> Loop
> Set rsMV = Nothing
> ElseIf Not IsNull(rs(0)) Then
> strOut = strOut & rs(0) & strSeparator
> End If
> rs.MoveNext
> Loop
> rs.Close
>
> 'Return the string without the trailing separator.
> lngLen = Len(strOut) - Len(strSeparator)
> If lngLen > 0 Then
> ConcatRelated = Left(strOut, lngLen)
> End If
>
> Exit_Handler:
> 'Clean up
> Set rsMV = Nothing
> Set rs = Nothing
> Exit Function
>
> Err_Handler:
> MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
> "ConcatRelated()"
> Resume Exit_Handler
> End Function
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar