Crosstab Problems

[csi95]

I'm really struggling to generate an MS Access report using a crosstab query. Hopefully someone here can help.

Using the guide provided in the Access 2000 Developer's Handbook, I've created the report and I'm attaching some VBA to label the columns correctly. This requires the creation of a RecordSet to get the field names. Unfortunately the open command for the recordset is crashing with an error that reads

The SELECT statement includes a reserved work or an argument name that is misspelled or missing, or the punctuation is incorrect.

This just isn't he case, so I'm lost.

Here's the SQL:

Code:

TRANSFORM COUNT(TrafficSource_ByWeek.traffic) AS TrafficSum
SELECT TrafficSource_ByWeek.StartOfWeek
FROM TrafficSource_ByWeek
GROUP BY TrafficSource_ByWeek.StartOfWeek
PIVOT TrafficSource_ByWeek.source;

And here's the VB Code:

Code:

    Dim rst As adodb.Recordset

    Set rst = New adodb.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.source = Me.RecordSource
    rst.Open options:=adCmdTable

I don't see anything exotic in the SQL or VBA. The SQL runs as a standard MS Access Query, it's just through VBA / ADO that it's choking.

Any ideas why?!?!?

Thanks in advance!

- Bryan

 

[Alisa]

I have no idea why your code isn't working, but since nobody smarter than me has responded to you yet, have you tried it in DAO instead?

Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    strSQL = "TRANSFORM COUNT(TrafficSource_ByWeek.traffic) AS TrafficSum" & Chr(10) & _
    "SELECT TrafficSource_ByWeek.StartOfWeek" & Chr(10) & _
    "From TrafficSource_ByWeek" & Chr(10) & _
    "GROUP BY TrafficSource_ByWeek.StartOfWeek" & Chr(10) & _
    "PIVOT TrafficSource_ByWeek.source;"
    Set qdf = CurrentDb.CreateQueryDef("", strSQL)
    Set rst = qdf.OpenRecordset

 

[csi95]

Alisa,

Thanks for the response. Isn't ADO the replacement for DAO?

In any event, I eventually just gave up on this and did it the hard way -- using VBA. I extract the data on the Report_Open then just fill in the fields on the Details_Print.

May not be as "elegant" as a Crosstab, but it works and I can stop banging my head against the wall!

- Bryan

 

[Alisa]

I don't know - glad to see you found something that works