locked
referring to column values in VBA Cross Tab SQL Statement RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    How do I refer to a column value from and Cross Tab (TRANSFORM) SQL Statement assigned as record set with dynamic column values.  I tried to build the text string statement that refers to the value of the dynamic column header.  When trying to assign the value to a numeric (double) variable I get a Type Mismatch error.

    For POOPTCounterInt = 1 To 52

        POOPTWkDmd(POOPTCounterInt) = "POOPTrst![" & POOPTCalendar(POOPTCounterInt) & "]"

    Next POOPTCounterInt

    POOPTrst![1/5/2016] = 200 (or some numeric value) where [1/5/2016] is a column header in the SQL statement that contains the umeric value


    Sunday, January 10, 2016 6:39 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Use a query to number the columns and then crosstab --

    SELECT ProjSubProj.ProjectID, ProjSubProj.ProjName, ProjSubProj.SubProjName, Sum(IIf([XX].[SubProjName]<=[ProjSubProj].[SubProjName],1,0)) AS SubProjNUM
    FROM ProjSubProj, ProjSubProj AS XX
    WHERE (((XX.ProjectID)=[ProjSubProj].[ProjectID]))
    GROUP BY ProjSubProj.ProjectID, ProjSubProj.ProjName, ProjSubProj.SubProjName;

    TRANSFORM First(SubProjNumbering.SubProjName) AS FirstOfSubProjName
    SELECT SubProjNumbering.ProjectID, SubProjNumbering.ProjName
    FROM SubProjNumbering
    GROUP BY SubProjNumbering.ProjectID, SubProjNumbering.ProjName
    PIVOT SubProjNumbering.SubProjNUM;

    EDIT -- If you need more help then post the SQL of your crosstab.

    Build a little, test a little


    Tuesday, January 12, 2016 12:33 AM