Crosstab Query Works In Query View Not In Recordset 1

[mdav2]

The user has a report requirement on some data that I can only display in a spreadsheet. I have written a spreadhsheet that formats the results of a crosstab quey into the format they want. The last stage is to get the data from ACCESS to EXCEL, which I was going to do in a recordset query. I have a crosstab query that works fine so thought it wouldn't be any trouble to replicate in a recordset but I can't get any data into a recordset.

The crosstab query that works fine when I run it in the query designer uses the following SQL:

TRANSFORM Sum([Q_HWRC-SS].NetTonnes) AS SumOfNetTonnes
SELECT T_SiteDetails.SiteName, [Q_HWRC-SS].OutputWasteType, [Q_HWRC-SS].UseTreatment
FROM (T_Months INNER JOIN [Q_HWRC-SS] ON T_Months.MonthRef = [Q_HWRC-SS].Month) INNER JOIN T_SiteDetails ON [Q_HWRC-SS].SiteID = T_SiteDetails.SiteID
WHERE ((([Q_HWRC-SS].OutputWasteType) Like [Forms]![F_Reports]![Waste]) AND ((T_SiteDetails.SiteName) Like "*HWRC*") AND (([Q_HWRC-SS].OutputDate) Between [Forms]![F_Reports]![FromDate] And [Forms]![F_Reports]![ToDate]))
GROUP BY T_SiteDetails.SiteName, [Q_HWRC-SS].OutputWasteType, [Q_HWRC-SS].UseTreatment
PIVOT T_Months.MonthName In ("Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar");

I need to create this as a recordset which I am doing with the following code:

    Dim cnCurrent As ADODB.Connection       ' Connection object
    Dim rst       As ADODB.Recordset        ' recordset object
    Dim sqlstr    As String

    'Instantiate connection object
    Set cnCurrent = CurrentProject.Connection
    
    ' Instantiate recordset object
    Set rst = New ADODB.Recordset

    ' Build Query
    sqlstr = ""

    sqlstr = sqlstr + "TRANSFORM Sum(T_Output.NetTonnes) AS SumOfNetTonnes "
    sqlstr = sqlstr + "SELECT T_Output.UseTreatment, T_SiteDetails.SiteName, T_Output.OutputWasteType, Sum(T_Output.NetTonnes) AS [Total Of NetTonnes] "
    sqlstr = sqlstr + "FROM T_SiteDetails RIGHT JOIN T_Output ON T_SiteDetails.SiteID = T_Output.SiteID "
    sqlstr = sqlstr + "WHERE (((T_SiteDetails.SiteName) Like '*HWRC*') AND ((T_Output.OutputWasteType) Like '" & [Forms]![F_Reports]![Waste] & "') AND ((T_Output.OutputDate) Between #04/01/2006# And #03/31/2007#)) "
    sqlstr = sqlstr + "GROUP BY T_Output.UseTreatment, T_SiteDetails.SiteName, T_Output.OutputWasteType, T_Output.OutputWasteType "
    sqlstr = sqlstr + "PIVOT Format([OutputDate],'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');"

    'Open recordset based on query
    rst.Open sqlstr, cnCurrent
    
    ' If count has returned 1 or more then display appropriate message
   If rst.BOF = True And rst.EOF = True Then
        MsgBox "You query has returned no data"
    Else
        ' CREATE SPREADSHEET
    End If

If I remove the where clause it works. If I try to filter the query in any way, even with a "LIKE *" in a text field, it always returns 0 records. I've removed all but one field and that makes no difference. I've tried removing the forms reference and that makes no difference either.

Anyone any advice on this one?

Thanks,

Mark.

Mark Davies
Warwickshire County Council

 

[Remou]

Just guessing, have you tried including a parameter?

 

[mdav2]

There are no parameters in the query, if you try and use one it complains that they are missing when you run it as a recordset but will still run as a query.

Mark Davies
Warwickshire County Council

 

[PHV]

Isn't [Forms]![F_Reports]![Waste] a parameter ? ....

 

[mdav2]

Well yes and no. It's not in the list of parameters you can add when creating a query, it is a filter. if you view them in SQL you have a parameter line added to your query.

I think the problem would seem to be with the recordset code, although it has always worked in the past. I have tried a very simple select with 1 field, 1 table and 1 filter and that does not appear to work. Even using a simple select like the one below:

sqlstr = sqlstr + "SELECT T_SiteDetails.SiteName "
sqlstr = sqlstr + "FROM T_SiteDetails "
sqlstr = sqlstr + "WHERE T_SiteDetails.SiteName Like '*' "

However, it now seems that it just doesn't like the * being used. It will work if you put a selection in from one of the values in the table. So I think I will simply only add this value if one has been selected (an if statement to include as part of the where clause).

Mark Davies
Warwickshire County Council

 

[Remou]

The wild card for ADO is %, not *

 

[mdav2]

Thanks REMOU the % has solved my annoying wildcard issue! It should be obvious as this is the wildcard in most database queries now. I got used to using the * in the DAO which I think used to work.

Thanks also to everyone for replying. This seems to have sorted out my issues.

Mark Davies
Warwickshire County Council