Re: Concatenate Function
- From: "Dirk Goldgar" <dg@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 15 Sep 2009 19:18:20 -0400
"Steve" <Steve@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:580244BA-0987-450A-B9A7-710CF6678808@xxxxxxxxxxxxxxxx
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 ------
--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
(please reply to the newsgroup)
.
- References:
- Concatenate Function
- From: Steve
- Concatenate Function
- Prev by Date: Re: Beetle? Can you help me out?
- Next by Date: RE: Allow Only One User to View a Given Record at A Time
- Previous by thread: Re: Concatenate Function
- Next by thread: Link to Outlook Calendar from Access
- Index(es):
Relevant Pages
|
