G
Guest
WinXP Home
MS Access 2003
I am trying to run a concatenate function in a query and I'm having a
hard time. I swiped a module from a member here on the board but I'm having
a hard time getting it to work. Whenever I run it, I get "Too Few
Parameters. Expected 1." I can't seem to put my finger on what I'm missing.
If someone could take a look that would be great. Both the query info and
the module info is below.
Query Info:
SELECT TestTable.Matchfield_Fam
DConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam] =" & TestTable.Matchfield_Fam) AS PartyMix
FROM TestTable;
Module Info:
Function DConcatenate( _
Expr As String, _
Domain As String, _
Optional Criteria As String = vbNullString, _
Optional Separator As String = ", " _
) As String
' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: A generic "concatenation" routine.
' Concatenates particular values from a specified set of
records.
'
' Expr An expression that identifies the field
' whose value you want to return.
' It can be a string expression identifying
' a field in a table or query, or it can be an
' expression that performs a calculation on data
' in that field.
' In Expr, you can include the name of a field in
a table,
' a control on a form, a constant, or a function.
If Expr
' includes a function, it can be either built-in
or user-defined,
' but not another domain aggregate or SQL
aggregate function.
' Domain A string expression identifying the set of
records that
' constitutes the domain.
' It can be a table name or a query name.
' Criteria An optional string expression used to restrict
the range of data
' on which the DConcatenate function is performed.
' For example, Criteria is often equivalent to
the WHERE clause in
' an SQL expression, without the word WHERE. If
criteria is omitted,
' the DConcatenate function evaluates Expr
against the entire domain.
' Any field that is included in criteria must
also be a field in Domain
' otherwise the DConcatenate function returns a
Null.
' Separator An optional string expression used to indicate
what character
' is supposed to be used to separate the
concatenated values.
' If not supplied, ", " (a comma followed by a
blank field) is used.
'
' Returns: A string representing the concatenation of the relevant set
of Expr in Domain,
' separated by Separator.
On Error GoTo Err_DConcatenate
Dim rstCurr As DAO.Recordset
Dim strConcatenate As String
Dim strSQL As String
strSQL = "SELECT " & Expr & " AS TheValue FROM " & Domain
If Len(Criteria) > 0 Then
strSQL = strSQL & " WHERE " & Criteria
End If
Set rstCurr = CurrentDb().OpenRecordset(strSQL)
Do While rstCurr.EOF = False
strConcatenate = strConcatenate & rstCurr!TheValue & Separator
rstCurr.MoveNext
Loop
If Len(strConcatenate) > 0 Then
strConcatenate = Left$(strConcatenate, Len(strConcatenate) -
Len(Separator))
End If
End_DConcatenate:
On Error Resume Next
rstCurr.Close
Set rstCurr = Nothing
DConcatenate = strConcatenate
Exit Function
Err_DConcatenate:
strConcatenate = vbNullString
Err.Raise Err.Number, "DConcatenate", Err.Description
Resume End_DConcatenate
End Function
MS Access 2003
I am trying to run a concatenate function in a query and I'm having a
hard time. I swiped a module from a member here on the board but I'm having
a hard time getting it to work. Whenever I run it, I get "Too Few
Parameters. Expected 1." I can't seem to put my finger on what I'm missing.
If someone could take a look that would be great. Both the query info and
the module info is below.
Query Info:
SELECT TestTable.Matchfield_Fam
DConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam] =" & TestTable.Matchfield_Fam) AS PartyMix
FROM TestTable;
Module Info:
Function DConcatenate( _
Expr As String, _
Domain As String, _
Optional Criteria As String = vbNullString, _
Optional Separator As String = ", " _
) As String
' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: A generic "concatenation" routine.
' Concatenates particular values from a specified set of
records.
'
' Expr An expression that identifies the field
' whose value you want to return.
' It can be a string expression identifying
' a field in a table or query, or it can be an
' expression that performs a calculation on data
' in that field.
' In Expr, you can include the name of a field in
a table,
' a control on a form, a constant, or a function.
If Expr
' includes a function, it can be either built-in
or user-defined,
' but not another domain aggregate or SQL
aggregate function.
' Domain A string expression identifying the set of
records that
' constitutes the domain.
' It can be a table name or a query name.
' Criteria An optional string expression used to restrict
the range of data
' on which the DConcatenate function is performed.
' For example, Criteria is often equivalent to
the WHERE clause in
' an SQL expression, without the word WHERE. If
criteria is omitted,
' the DConcatenate function evaluates Expr
against the entire domain.
' Any field that is included in criteria must
also be a field in Domain
' otherwise the DConcatenate function returns a
Null.
' Separator An optional string expression used to indicate
what character
' is supposed to be used to separate the
concatenated values.
' If not supplied, ", " (a comma followed by a
blank field) is used.
'
' Returns: A string representing the concatenation of the relevant set
of Expr in Domain,
' separated by Separator.
On Error GoTo Err_DConcatenate
Dim rstCurr As DAO.Recordset
Dim strConcatenate As String
Dim strSQL As String
strSQL = "SELECT " & Expr & " AS TheValue FROM " & Domain
If Len(Criteria) > 0 Then
strSQL = strSQL & " WHERE " & Criteria
End If
Set rstCurr = CurrentDb().OpenRecordset(strSQL)
Do While rstCurr.EOF = False
strConcatenate = strConcatenate & rstCurr!TheValue & Separator
rstCurr.MoveNext
Loop
If Len(strConcatenate) > 0 Then
strConcatenate = Left$(strConcatenate, Len(strConcatenate) -
Len(Separator))
End If
End_DConcatenate:
On Error Resume Next
rstCurr.Close
Set rstCurr = Nothing
DConcatenate = strConcatenate
Exit Function
Err_DConcatenate:
strConcatenate = vbNullString
Err.Raise Err.Number, "DConcatenate", Err.Description
Resume End_DConcatenate
End Function