query sql word convert into vba code

[polyubi]

I have a cross query which works well, it is:

TRANSFORM Sum(t.stockqty) AS sumofstockqty
SELECT t.pncode, t.unit
FROM [SELECT querystock.pncode, querystock.unit, querystock.stockDate, querystock.PO, querystock.stockqty FROM querystock UNION ALL select 'Total' as pncode,null as unit,stockDate,null as PO,ttl as stockqty from query]. AS t
GROUP BY t.pncode, t.unit
PIVOT Format([stockDate],'yyyy-mm-dd');
*****************
Now for the purpose of query form, I want to convert the above into vba code:

strSQL = "TRANSFORM Sum(t.stockqty) AS sumofstockqty SELECT t.pncode, t.unit " & _
"FROM [SELECT querystock.pncode, querystock.unit, querystock.stockDate, querystock.PO, querystock.stockqty FROM querystock "
strSQL = strSQL & "WHERE " & strWhere
strSQL = strSQL & " UNION ALL select 'Total' as pncode,null as unit,stockDate,null as PO,ttl as stockqty from query]. as t "
strSQL = strSQL & " GROUP BY t.pncode,t.unit PIVOT Format([stockDate],'yyyy-mm-dd'); "

but this cannot go through, hope any people help me out.

 

[NigelShaw]

I have a cross query which works well, it is:

TRANSFORM Sum(t.stockqty) AS sumofstockqty
SELECT t.pncode, t.unit
FROM [SELECT querystock.pncode, querystock.unit, querystock.stockDate, querystock.PO, querystock.stockqty FROM querystock UNION ALL select 'Total' as pncode,null as unit,stockDate,null as PO,ttl as stockqty from query]. AS t
GROUP BY t.pncode, t.unit
PIVOT Format([stockDate],'yyyy-mm-dd');
*****************
Now for the purpose of query form, I want to convert the above into vba code:

strSQL = "TRANSFORM Sum(t.stockqty) AS sumofstockqty SELECT t.pncode, t.unit " & _
"FROM [SELECT querystock.pncode, querystock.unit, querystock.stockDate, querystock.PO, querystock.stockqty FROM querystock "
strSQL = strSQL & "WHERE " & strWhere
strSQL = strSQL & " UNION ALL select 'Total' as pncode,null as unit,stockDate,null as PO,ttl as stockqty from query]. as t "
strSQL = strSQL & " GROUP BY t.pncode,t.unit PIVOT Format([stockDate],'yyyy-mm-dd'); "

but this cannot go through, hope any people help me out.

Hi

what method are you using to try and run the sql? it will not run itself.

check out-

QueryDefs
RunSQL


Nidge

 

[polyubi]

it is on the click event, in full it goes like this:
******************
Dim strWhere As String
Dim qdf As DAO.QueryDef
Dim strSQL As String
strWhere = ""
If Not IsNull(Me.pncode) Then
strWhere = strWhere & "[pncode] like '*" & Me.pncode & "*' AND "
End If
If Not IsNull(Me.stockDateStart) Then
strWhere = strWhere & "[stockDate] >= #" & Format(Me.stockdateStart, "yyyy-mm-dd" & "#) AND "
End If
If Not IsNull(Me.stockDateEnd) Then
strWhere = strWhere & "([stockDate] <= #" & Format(Me.stockdateEnd, "yyyy-mm-dd") & "#) AND "
End If
If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If
.........

 

[The_Doc_Man]

Somewhere in there near the end, you need a

DoCMD.OpenQuery strWhere, ...

and some parameters for the OpenQuery. Since this is a SELECT, running the query will do no good. But opening it, perhaps read-only (one of the options of OpenQuery), would show you what you wanted to know, I think.

 

[polyubi]

strSQL = "TRANSFORM Sum(t.stockqty) AS sumofstockqty SELECT t.pncode, t.unit " & _
"FROM [SELECT querystock.pncode, querystock.unit, querystock.stockDate, querystock.PO, querystock.stockqty FROM querystock "
strSQL = strSQL & "WHERE " & strWhere
strSQL = strSQL & " UNION ALL select 'Total' as pncode,null as unit,stockDate,null as PO,ttl as stockqty from query]. as t "
strSQL = strSQL & " GROUP BY t.pncode,t.unit PIVOT Format([stockDate],'yyyy-mm-dd'); "

********************

something wrong with the code highlighted with red. Please let me know if it is a correct place.