Creating dynamic query headers

[TomLon]

I have a simple select query created in MS Access 2000. It is simple, however, it is based on about 8 other queries. The SQL statement is as follows:

******************************************************
SELECT qry_EISComparison_CurYrLastYrTotals.Division, qry_EISComparison_CurYrLastYrTotals.CurMonthLbs, qry_EISComparison_CurYrLastYrTotals.CurMnthYTDLbs, qry_EISComparison_CurYrLastYrTotals.CurMnthLastYrLbs, qry_EISComparison_CurYrLastYrTotals.CurMnthLastYrYTDLbs
FROM qry_EISComparison_CurYrLastYrTotals;
*******************************************************

What I need to do is to modify the query headers for the CurMonthLbs, CurMnthYTDlbs, CurMnthLastYtLbs, and CurMnthLastYrYTDLBs fields dynamically. These headers will be based upon parameters that the user enters into the form. I can capture the desired header names fine and push it to variables; but what SQL statement would I use to allow me to modify the column headers to say for instance:

CurMnthLbs would become Jun-04
CurMnthYTDLbs would become Jun-04 YTD.

Now of course, I would need to do this dynamically. Thanks

 

[PHV]

Build your query dynamically with the aliases you want:
strAlias1 = "Jun-04"
strSQL = "SELECT CurMnthLbs As " & strAlias1 & ",..."

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

 

[dhookom]

Is the query being output to a report? If so, you shouldn't change the column names in the query. If you really need to change the column names then you will need to use DAO to modify the SQL property of the querydef.

Dim strSQL as String
strSQL = "SELECT Division, " & _
   "CurMonthLbs As [" & Forms!frmMyForm!txtCurMthLbs & "], " & _
   "CurMnthYTDLbs As [" & Forms!frmMyForm!txtCurMthYTDLbs & "], " & _ 
   "...  ;"
Currentdb.QueryDefs("qryYourQuery").SQL = strSQL

Duane
MS Access MVP
Find out how to get great answers faq219-2884.