Column Headings

[errolf]

I am using a Form to run a crosstab query that has date column headings, I want the column headings to change according to the dates in the selected parameters from the input form.
I am us the following SQL at present
Private Sub CmdSelect_Click()
Dim db As DAO.database
Set db = CurrentDb
db.QueryDefs("TEST&quot.Sql = "TRANSFORM " & _
"Sum(round([NETT]-[gst])) AS Sales " & _
"SELECT [SCEX].[AC_NO], [DREX].[NAME], [DREX].[CLASS], retgrp([SCEX].[CLASS]) AS GRP, [Forms]![StatsSboard]![Classif] AS Sel, [CarrTerrTbl].[NAME]as TERRITORY," & _
Forms!statssboard!TST & _
"FROM (((SCEX INNER JOIN DREX ON [SCEX].[AC_NO]=[DREX].[NUMBER])" & _
"INNER JOIN STEX ON [SCEX].[STOCK]=[STEX].

)" & _
"INNER JOIN CarSalespersonTbl ON [DREX].[SMAN]=[CarSalespersonTbl].[REFERENCE])" & _
"INNER JOIN CarrTerrTbl ON [DREX].[TERR]=[CarrTerrTbl].[REFERENCE]" & _
"WHERE " & _
"(((retgrp([drex].[CLASS]))" & _
"Like [Forms]![StatsSboard]![Classif])" & _
"And (([STEX].[SUPP_2]) Like [Forms]![StatsSboard]![Combo2]) And (([SCEX].[INV_DATE])" & _
"[COLOR=red]Between [Forms]![StatsSboard]![BeginningDate] And [Forms]![StatsSboard]![EndingDate])[COLOR=black] And (([CarSalespersonTbl].[NAME]) Like [Forms]![StatsSboard]![Spers]) And (([CarrTerrTbl].[NAME])" & _
"Like [Forms]![StatsSboard]![CmbTerr]))" & _
"GROUP BY [SCEX].[AC_NO], [DREX].[NAME], [DREX].[CLASS], [SCEX].[MG_GRP], [STEX].[SUPP_2], retgrp([SCEX].[CLASS]), [Forms]![StatsSboard]![Classif], [CarrTerrTbl].[NAME]," & _
Forms!statssboard!TST & _
 _
"PIVOT Format([INV_DATE],'mmm-yy')" & _
"[COLOR=Red]In " & _
"('Apr-02','May-02','Jun-02','Jul-02','Aug-02','Sep-02','Oct-02','Nov-02','Dec-02','Jan-03','Feb-03');"[COLOR=black]
DoCmd.OpenQuery "TEST", acViewNormal
End Sub
 
Is there any way to do this by dynamically changing the "IN" statement in the SQL according to the dates in the Beginning date and ending date controls in the form.

In anticipation

Errol Featherstone

 

[BillPower]

Hi Errol,

This should work ok, I've highlighted any changes/additions in blue:

Private Sub CmdSelect_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim intMonths As Integer, i As Integer
Dim datStart As Date, datEnd As Date
Dim strQSL As String, strDatesQSL As String, strPivotQSL As String
datStart = Me!BeginningDate
datEnd = Me!EndingDate
intMonths = DateDiff("m", datStart, datEnd)
While i <= intMonths
strDatesQSL = Format(DateAdd(&quot;m&quot;, i, datStart), &quot;mmm-yy&quot
If i < intMonths Then
strPivotQSL = strPivotQSL & &quot;'&quot; & strDatesQSL & &quot;',&quot;
Else
strPivotQSL = strPivotQSL & &quot;'&quot; & strDatesQSL & &quot;'&quot;
End If
i = i + 1
Wend

db.QueryDefs(&quot;TEST&quot.SQL = &quot;TRANSFORM &quot; & _
&quot;Sum(round([NETT]-[gst])) AS Sales &quot; & _
&quot;SELECT [SCEX].[AC_NO], [DREX].[NAME], [DREX].[CLASS], retgrp([SCEX].[CLASS]) AS GRP, [Forms]![StatsSboard]![Classif] AS Sel, [CarrTerrTbl].[NAME]as TERRITORY,&quot; & _
Forms!statssboard!TST & _
&quot;FROM (((SCEX INNER JOIN DREX ON [SCEX].[AC_NO]=[DREX].[NUMBER])&quot; & _
&quot;INNER JOIN STEX ON [SCEX].[STOCK]=[STEX].ÿ)&quot; & _
&quot;INNER JOIN CarSalespersonTbl ON [DREX].[SMAN]=[CarSalespersonTbl].[REFERENCE])&quot; & _
&quot;INNER JOIN CarrTerrTbl ON [DREX].[TERR]=[CarrTerrTbl].[REFERENCE]&quot; & _
&quot;WHERE &quot; & _
&quot;(((retgrp([drex].[CLASS]))&quot; & _
&quot;Like [Forms]![StatsSboard]![Classif])&quot; & _
&quot;And (([STEX].[SUPP_2]) Like [Forms]![StatsSboard]![Combo2]) And (([SCEX].[INV_DATE])&quot; & _
&quot;Between [Forms]![StatsSboard]![BeginningDate] And [Forms]![StatsSboard]![EndingDate]) And (([CarSalespersonTbl].[NAME]) Like [Forms]![StatsSboard]![Spers]) And (([CarrTerrTbl].[NAME])&quot; & _
&quot;Like [Forms]![StatsSboard]![CmbTerr]))&quot; & _
&quot;GROUP BY [SCEX].[AC_NO], [DREX].[NAME], [DREX].[CLASS], [SCEX].[MG_GRP], [STEX].[SUPP_2], retgrp([SCEX].[CLASS]), [Forms]![StatsSboard]![Classif], [CarrTerrTbl].[NAME],&quot; & _
Forms!statssboard!TST & _
_
&quot;PIVOT SCEX.INV_DATE&quot; & _
&quot;In &quot; & _
&quot;(&quot; & strPivotQSL & &quot;&quot;
DoCmd.OpenQuery &quot;TEST&quot;, acViewNormal
End Sub

Let me know how you get on.

 

[errolf]

Hi Bill,
Thanks for you reply it work great except I get no data in the columns can you help with this

Errol

 

[BillPower]

Can you send me your DB at billpower@cwcom.net

 

[errolf]

Hi Bill,
Thanks for you prompt reply,
I got it to work by changing the pivot statement to
&quot;PIVOT Format(SCEX.INV_DATE,'mmm-yy') &quot; & _
&quot;In &quot; & _
&quot;(&quot; & strPivotQSL & &quot;&quot;

Is it possible to use this routine to change column headings to yearly format?

Best Regards and many thanks

Errol