Too Few Parameters Problem w/Query

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
' http://I.Am/DougSteele
' 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

What's the data type of Matchfield_Fam? If it's text, you need quotes around
the criteria you're passing:

DConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam]
='" & TestTable.Matchfield_Fam & "'")

or

DConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam]
=" & Chr$(34) & TestTable.Matchfield_Fam & Chr$(34))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jake Leis" wrote in message
...
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
' http://I.Am/DougSteele
' 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

Thanks a lot Doug, that worked perfectly.

"Douglas J. Steele" wrote:

What's the data type of Matchfield_Fam? If it's text, you need quotes around
the criteria you're passing:

DConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam]
='" & TestTable.Matchfield_Fam & "'")

or

DConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam]
=" & Chr$(34) & TestTable.Matchfield_Fam & Chr$(34))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jake Leis" wrote in message
...
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
' http://I.Am/DougSteele
' 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

Hmm, when I run the query it grinds for about an hour and then says there
isn't enough disk space. My table is about 75k records. Thoughts?


UPDATE SD20_VoterHistory, Sept22_AugVF_SD20_WithHistory_RDUABSReq SET
Sept22_AugVF_SD20_WithHistory_RDUABSReq.PartyMix =
DConcatenate("[Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Party]","[Sept22_AugVF_SD20_WithHistory_RDUABSReq]","[Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Matchfield_Fam]
='" & [Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Matchfield_Fam] & "'");


"Douglas J. Steele" wrote:

What's the data type of Matchfield_Fam? If it's text, you need quotes around
the criteria you're passing:

DConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam]
='" & TestTable.Matchfield_Fam & "'")

or

DConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam]
=" & Chr$(34) & TestTable.Matchfield_Fam & Chr$(34))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jake Leis" wrote in message
...
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
' http://I.Am/DougSteele
' 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

I think you'll find that you're creating a cartesian product between two
table SD20_VoterHistory and Sept22_AugVF_SD20_WithHistory_RDUABSReq.

Does this work any better:

UPDATE SD20_VoterHistory
SET Sept22_AugVF_SD20_WithHistory_RDUABSReq.PartyMix =
DConcatenate("[Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Party]","[Sept22_AugVF_SD20_WithHistory_RDUABSReq]","[Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Matchfield_Fam] ='" & [Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Matchfield_Fam] & "'");--Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no private e-mails, please)"Jake Leis" wrote in ... Hmm, when I run the query it grinds for about an hour and then says there isn't enough disk space. My table is about 75k records. Thoughts? UPDATE SD20_VoterHistory, Sept22_AugVF_SD20_WithHistory_RDUABSReq SET Sept22_AugVF_SD20_WithHistory_RDUABSReq.PartyMix =DConcatenate("[Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Party]","[Sept22_AugVF_SD20_WithHistory_RDUABSReq]","[Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Matchfield_Fam] ='" & [Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Matchfield_Fam] & "'"); "Douglas J. Steele" wrote: What's the data type of Matchfield_Fam? If it's text, you need quotesaround the criteria you're passing:DConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam] ='" & TestTable.Matchfield_Fam & "'") orDConcatenate("[TestTable].[Party]","[TestTable]","[TestTable].[Matchfield_Fam] =" & Chr$(34) & TestTable.Matchfield_Fam & Chr$(34)) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Jake Leis" wrote in message ... WinXP Home MS Access 2003 I am trying to run a concatenate function in a query and I'm havinga 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 infoand 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 ' http://I.Am/DougSteele ' 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 bean ' expression that performs a calculation on data ' in that field. ' In Expr, you can include the name of afield 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 equivalentto 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 criteriamust also be a field in Domain ' otherwise the DConcatenate functionreturns 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 bya 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

I have no idea what happened with that response! I've trimmed off the
unnecessary bits.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Douglas J. Steele" wrote in message
...
I think you'll find that you're creating a cartesian product between two
table SD20_VoterHistory and Sept22_AugVF_SD20_WithHistory_RDUABSReq.

Does this work any better:

UPDATE SD20_VoterHistory
SET Sept22_AugVF_SD20_WithHistory_RDUABSReq.PartyMix =
DConcatenate("[Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Party]","[Sept22_AugVF_SD20_WithHistory_RDUABSReq]","[Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Matchfield_Fam]
='" & [Sept22_AugVF_SD20_WithHistory_RDUABSReq].[Matchfield_Fam] & "'");