Dynamically Creating Column Names in a Query

[tbonehwd]

I guess I am just having to much fun today. First off I would like to again thank PHV for helping with the following code. But my next question, I have the user entering a date on the form in a field called txtFRMDATE and the code below will report on that week and that week -1 and -2 is there a way to have those dates populate strTEST, strTEST1 and strTEST2 so we know what date we are reporting on rather than just doing something like week1 Week2 etc...

Thanks in advance,

Terry

Dim strFRMDATE As String
Dim strTEST As String
Dim strTEST1 As String
Dim strTEST2 As String

strFRMDATE = "#" & Me.txtFRMDATE.Value & "#"

strTEST = "12/4/2005"
strTEST1 = "11/27/2005"
strTEST2 = "11/20/2005"

"SELECT DATA.ISBN, DATA.CYP_UNITS_SOLD AS [" & strTEST & "] " & _
", DATA_1.CYP_UNITS_SOLD AS [" & strTEST1 & "]" & _
", DATA_2.CYP_UNITS_SOLD AS [" & strTEST2 & "] " & _
"FROM ((DATA LEFT JOIN DATA AS DATA_1 ON DATA.ISBN=DATA_1.ISBN) " & _
"LEFT JOIN DATA AS DATA_2 ON DATA.ISBN=DATA_2.ISBN)" & _
"WHERE ((DATA.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")) " & _
"AND ((DATA_1.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")-1) " & _
"AND ((DATA_2.WEEKNO)=DATEPART('ww'," & strFRMDATE & ")+1) " & _
"AND DATA.CMT_CODE='AMZ' " & _
"AND DATA_1.CMT_CODE='AMZ' " & _
"AND DATA_2.CMT_CODE='AMZ'" & _
"AND ((YEAR(DATA.WEEK))=YEAR(" & strFRMDATE & "))" & _
"AND ((YEAR(DATA_1.WEEK))=YEAR(" & strFRMDATE & "))" & _
"AND ((YEAR(DATA_2.WEEK))=YEAR(" & strFRMDATE & "))" & _
";"

 

[PHV]

Something like this ?
strTEST = Me.txtFRMDATE.Value
strTEST1 = Me.txtFRMDATE.Value - 7
strTEST2 = Me.txtFRMDATE.Value - 14

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886