Hello everyone,

Do you know if ASP supports Transform query?

Here is the my code:

strMonth = "TRANSFORM Count([PrimaryKey]) AS CountOfPrimaryKey SELECT (Format([Received Date],'MMM')) AS sMonth FROM [2005 Received Records] WHERE SUPPLIER = '" &Supp& "' GROUP BY (Year([Received Date])*12+Month([Received Date])-1), (Format([Received Date],'MMM')) PIVOT [SubLate]"

When I first run the same query, successfully return the expected data. After that the recordset eventually has began to come back empty all the time.

Any help would be appreciated...

Thanks,

Some how I think your leaving out some of the code. Are you querying an Access or MYSQL Datastore? Usually when I get this kind of problem where the query works fine the first time and then returns an error or comes up empty its because instead of de-referencing my recordsets I just use the Resposne.End. So instead of:

I just use
Give it a try, I don't have the technical explanation as to why this works with me now but will post it later.

Hi,

I am querying Access datastore. I used Response.End instead of
rsMonth.Close()
set rsMonth=Nothing

but it didn't work. This problem is making me crazy. I don't get any error, I just get empty recordset all the time. The query works fine in Access environment.

This is my code:

strMonth = "TRANSFORM Count([2005 Received Records].PrimaryKey) AS CountOfPrimaryKey SELECT (Format([Received Date],'mmm')) AS [Month] FROM [2005 Received Records] GROUP BY (Year([Received Date])*12+Month([Received Date])-1), (Format([Received Date],'mmm')) PIVOT [2005 Received Records].SubLate"
set rsMonth= Server.CreateObject("ADODB.Recordset")
rsMonth.Open strMonth, objConn

    <tr>
        <td class="TitleName"><b>Month</b></td>
        <td class="TitleName"><b>Late#</b></td>
        <td class="TitleName"><b>Total#</b></td>
    </tr>


<%Do While Not rsMonth.EOF%>
    <tr>
        <td><center><b><%=rsMonth("Month")%></b></center></td>
        <td><center><b>><%=rsMonth("Late")%></b></center></td>
        <td><center><b>><%=rsMonth("On Time")%>
    </tr>
        <%rsMonth.MoveNext
        Loop%>

rsMonth.Close()
set rsMonth=Nothing


Thank you for your help...