How to use variable column names

[Ben_Entrew]

Hi all,

how can I use the column repmonth in the following statement?

repmonth is as a string declared and given by the user, in my case repmonth = 112013.


Thanks in advance.

Regards,
Ben

Public Sub TEST2()
DoCmd.RunSQL "SELECT PPC_REPORT.Reporting_Month, Report_Products.Division, Report_Products.Product_Class, PPC_REPORT.Sales_Quantity, Sales_QUANTITY_BP_2013.'" & repmonth & "' , Sales_QUANTITY_BP_2013.Total, Sales_Quantity_'" & CF_PERIOD & "'.' & repmonth & "', Sales_Quantity_'" & CF_PERIOD & "'.Total INTO QUANTITIES_TEMP " & _
             " FROM ((Report_Products LEFT JOIN PPC_REPORT ON Report_Products.Product_Class = PPC_REPORT.Product_Class) LEFT JOIN Sales_QUANTITY_BP_2013 ON Report_Products.Product_Class = Sales_QUANTITY_BP_2013.[Product Groups]) LEFT JOIN Sales_Quantity_'" & CF_PERIOD & "' ON Report_Products.Product_Class = Sales_Quantity_'" & CF_PERIOD & "'.[Product Groups]"

End Sub

 

[pbaldy]

You're properly concatenating in the SELECT clause, but you don't want the single quotes there. If anything, replace them with [] just in case the field name has spaces or symbols in it.

 

[Ben_Entrew]

Thanks Paul,

you helped me out once again.

Regards,
Ben

 

[pbaldy]

No problem Ben!