Concatenate Function

[Steve]

Greetings:

I am attempting to use Duane Hookom's concatenate function (reproduced
below) to concatenate the results of a query named "qryMapMethod". When I
type:

Concatenate("qryMapMethod") into the immediate window I get the following
error:

"Runtime Error 3061. Too few parameters, expected 1" when it attempts to
execute "Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)" line of code.

Any help in what I am doing wrong would be greatly appreciated. Thanks.

Steve

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = "; ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
--

 

[Ken Snell MVP]

What field(s) does qryMapMethod return? You must give an SQL statement to
the function that identifies the field that you want to concatenate into one
string. For example:

Concatenate("SELECT Field1 FROM qryMapMethod")

 

[Steve]

Thanks Ken,

If I pass the SQL statement to the function as below, it works fine. Thanks.

Concatenate("SELECT tblMapMethod.chrMapMethod FROM tblMapMethod;").

Is there a way to write the function so that you can pass the name of a
query instead of having to pass the SQL statement itself? Thanks again.

 

[Ken Snell MVP]

Well, yes, but how will the function be told what field to use?

 

[Douglas J. Steele]

Try using:

Concatenate(CurrentDb.QueryDefs("qryMapMethod").SQL)

 

[John Spencer]

Sure there is. You might try

Set db = CurrentDb

'Add this line that assumes that the query String will always start with
'SELECT plus a space. If it doesn't then it assumes the pstrSQL is the
'name of a query. I would probably institute something that checked
'for the existence of such a query and that the query was a SELECT query.
'Also, they query could not have any parameters in it, so I would suspect
'doing this would be close to useless.

IF Not(pStrSQL Like "SELECT *") THEN
pstrSQL = db.QueryDefs(pStrSQL).SQL
End if

Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dirk Goldgar]

Greetings:

I am attempting to use Duane Hookom's concatenate function (reproduced
below) to concatenate the results of a query named "qryMapMethod". When I
type:

Concatenate("qryMapMethod") into the immediate window I get the following
error:

"Runtime Error 3061. Too few parameters, expected 1" when it attempts to
execute "Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)" line of code.

Any help in what I am doing wrong would be greatly appreciated. Thanks.

Steve

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ";
") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
--

Pardon me, but I think there may be some confusion here. Duane's
Concatenate function works just fine if you pass it the name of a stored
query, so long as that query has no parameters. My guess is that
qryMapMethod has a parameter, such as (maybe) a reference to a control on a
form. Unlike what Access does when you open a query using DoCmd.OpenQuery,
DAO doesn't automatically resolve parameters.

If this is the reason for the error you're getting, you could modify Duane's
function to get Access to resolve the parameters for you, like this:

'------ start of modified code ------
Function Concatenate( _
pstrSQL As String, _
Optional pstrDelim As String = "; ") _
As String

'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'
' Modified by Dirk Goldgar, 15 September 2009, to resolve
' parameter references in stored queries.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb

If pstrSQL Like "SELECT *" Then
Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)
Else
Set qdf = db.QueryDefs(pstrSQL)
With qdf
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = .OpenRecordset(dbOpenDynaset)
End With
End If

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With

Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
Set qdf = Nothing

If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If

Concatenate = strConcat

End Function
'------ end of modified code ------