Re: 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" <JakeLeis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C77565C3-6A3C-4ED9-ADF4-0FBE77F90D90@xxxxxxxxxxxxxxxx
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 AccessHelp@xxxxxxxxxx
' 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



.

Relevant Pages

  • Too Few Parameters Problem w/Query
    ... I am trying to run a concatenate function in a query and I'm having a ... Domain As String, _ ... ' Description: A generic "concatenation" routine. ... Dim strConcatenate As String ...
    (microsoft.public.access.gettingstarted)
  • Re: Too Few Parameters Problem w/Query
    ... Domain As String, _ ... Optional Criteria As String = vbNullString, ... ' Description: A generic "concatenation" routine. ... Dim strConcatenate As String ...
    (microsoft.public.access.gettingstarted)
  • RE: Concatenated Field - Sort Order
    ... concatenation string, this same data was not accurate. ... I have a concatenated field in a main query set as follows: ...
    (microsoft.public.access.queries)
  • Re: Aggregate string concatenation efficiency problem
    ... that involves a query and also involves a string that is being ... The string concatenation operation? ... data from Column5, the column in question, things slow way down. ... The string concatenation that I perform with the string builder ...
    (comp.databases.ms-access)
  • Re: Using Variables in an INSERT INTO query
    ... when you want to concatenation a string you use ... when you want to concatenation variables with a string you use ... I want to use the query to append a single row ... > Dim qdf As QueryDef ...
    (microsoft.public.access.queries)