If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Too Few Parameters Problem w/Query
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 |
#3
|
|||
|
|||
Too Few Parameters Problem w/Query
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 |
#4
|
|||
|
|||
Too Few Parameters Problem w/Query
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 |
#5
|
|||
|
|||
Too Few Parameters Problem w/Query
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 |
#6
|
|||
|
|||
Too Few Parameters Problem w/Query
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] & "'"); |
Thread Tools | |
Display Modes | |
|
|