Creating string to use as dynamic Crosstab column headings

[Guest]

I am trying to create a string that I want to assign to a variable that I can
use to insert dynamic column headings in a sql statement. The following is
the code im using, but Im having problems inserting the required "'s around
the report names as it runs throught he loop. Im not sure of the syntax to
insert the "'s. Anyone please?

'open Database Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strPathAutoRaw &
"CostExceptionData.mdb;"
.Open
End With

'Create reportname string for SQL
strSQLReports = "SELECT tblReportData.RptName " _
& "FROM tblReportData " _
& "GROUP BY tblReportData.RptName " _
& "ORDER BY tblReportData.RptName; "
rsReports.Open strSQLReports, cn, adOpenKeyset, adLockReadOnly
rsReports.MoveFirst


For intRptCounter = 1 To rsReports.RecordCount
strReportNames = strReportNames & "; " & rsReports.Fields(0).Value
'i need to insert "'s in the variable before and after each report name so i
can insert it in the SQL
rsReports.MoveNext
Next

 

[Duane Hookom]

I don't see anything in your code that suggests a crosstab query. However,
there is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.