Concatenate results of query

[Steve]

Hi,

I am using Duane Hookom's method for concatenating a string reproduced below
but I would like to simply pass the name of a query to the function rather
than sending the whole SQL statement. But when I try to do this I get
"Run-time Error 3078 - The Microsoft Jet database engine cannot find the
input table or query '[qryProc]' . Make sure it exists and its name is
spelled correctly". Is it possible to modify how I am using Mr. Hookom's code
so that I can specify a recordset defined by an existing query by passing the
query name to the function?

Text box on Form1 Control Source:
=Concatenate("[qryProc]")

qryProc SQL statement:
SELECT tblProcedure.PtDxID, tlkpMasterProcList.Procedure
FROM tblProcedure INNER JOIN tlkpMasterProcList ON tblProcedure.ProcID =
tlkpMasterProcList.ProcID
WHERE (((tblProcedure.PtDxID)=[Forms]![Form1]![PtDxID]));

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)

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

 

[Carl Rapson]

Try removing the square brackets "[]" from your Concatenate call:

=Concatenate("qryProc")

Carl Rapson

 

[Steve]

Thanks for the suggestion. I tried that and I get:
Run-time error 3061. Too few parameters. expected 1. Any other ideas? Thanks
again. Steve

Try removing the square brackets "[]" from your Concatenate call:

=Concatenate("qryProc")

Carl Rapson

Hi,

I am using Duane Hookom's method for concatenating a string reproduced
below
but I would like to simply pass the name of a query to the function rather
than sending the whole SQL statement. But when I try to do this I get
"Run-time Error 3078 - The Microsoft Jet database engine cannot find the
input table or query '[qryProc]' . Make sure it exists and its name is
spelled correctly". Is it possible to modify how I am using Mr. Hookom's
code
so that I can specify a recordset defined by an existing query by passing
the
query name to the function?

Text box on Form1 Control Source:
=Concatenate("[qryProc]")

qryProc SQL statement:
SELECT tblProcedure.PtDxID, tlkpMasterProcList.Procedure
FROM tblProcedure INNER JOIN tlkpMasterProcList ON tblProcedure.ProcID =
tlkpMasterProcList.ProcID
WHERE (((tblProcedure.PtDxID)=[Forms]![Form1]![PtDxID]));

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)

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

 

[Carl Rapson]

Only two possibilities come to mind. In your OpenRecordset call, you might
try

Set rs = db.OpenRecordset("SELECT * FROM " & pstrSQL)

just to see if it's a case of the OpenRecordset not liking having only the
name of a query (I know, it should work). The other possibility is that it
has something to do with the reference to the form field in your query. Does
your query run correctly stand-alone? If you hard-code a value in place of
the reference to the form field, do you still get the same error?

Carl Rapson

Thanks for the suggestion. I tried that and I get:
Run-time error 3061. Too few parameters. expected 1. Any other ideas?
Thanks
again. Steve

Try removing the square brackets "[]" from your Concatenate call:

=Concatenate("qryProc")

Carl Rapson

Hi,

<snipped>

 

[Steve]

Thanks. I will try that. One other thought came to mind. Do you think the
problem is that pstrSQL is defined as a string variable instead of an object
or query. I played around with Dim pstrSQL as QueryDef or as Object but
couldn't get that to work either.

Only two possibilities come to mind. In your OpenRecordset call, you might
try

Set rs = db.OpenRecordset("SELECT * FROM " & pstrSQL)

just to see if it's a case of the OpenRecordset not liking having only the
name of a query (I know, it should work). The other possibility is that it
has something to do with the reference to the form field in your query. Does
your query run correctly stand-alone? If you hard-code a value in place of
the reference to the form field, do you still get the same error?

Carl Rapson

Thanks for the suggestion. I tried that and I get:
Run-time error 3061. Too few parameters. expected 1. Any other ideas?
Thanks
again. Steve

Try removing the square brackets "[]" from your Concatenate call:

=Concatenate("qryProc")

Carl Rapson

Hi,

<snipped>

 

[Carl Rapson]

I don't think so, because OpenRecordset takes a string for the first
parameter. So it should work, I would think. But it will be interesting to
see if my first suggestion works.

Carl Rapson