Re: Concatenate results of query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

=Concatenate("qryProc")

Carl Rapson

"Steve" <Steve@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AEA630C8-A473-4341-9752-D6D8A25ADD22@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: combined text fields using a GROUP BY statement
    ... query and module code are below. ... Function Concatenate(pstrSQL As String, _ ... 'tblFamMem with FamID, FirstName, DOB,... ... Dim strConcat As String 'build return string ...
    (microsoft.public.access.queries)
  • Re: combined text fields using a GROUP BY statement
    ... query and module code are below. ... Function Concatenate(pstrSQL As String, _ ... 'tblFamMem with FamID, FirstName, DOB,... ... Dim strConcat As String 'build return string ...
    (microsoft.public.access.queries)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Concatenate results of query
    ... I am using Duane Hookom's method for concatenating a string reproduced ... input table or query ''. ... Dim strConcat As String 'build return string ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)