Query Field Name : Variable

[Steven]

Is it possible to use a function to return a variable to be the Field Name in
a query.

I have used for example: retval() in the Criteria section of a query but I
cannot make that work in the field name.

Can this be done?

Thank you,

Steven

 

[John W. Vinson]

Is it possible to use a function to return a variable to be the Field Name in
a query.

Only by constructing the entire SQL string for the query in code. The query
engine cannot see VBA variables.

The need to do this makes me suspect that your table design might be wrong -
could you explain the context? It would be very rare to have a variable
fieldname!

 

[Steven]

John,

Thank you for your response. What I want to do is export a query to excel.
I have a query with Totals. I have 3 years that will be totaled grouped by
Category.
The final will look like for example:
Category | Year 2007 | Year 2008 | Year 2009
group sum sum sum

The issue is that it will not always be 2007 ; 2008 and 2009. That is
where I want to use a function, "or something", to be able to give the Field
alias the correct year for the headings when it exports to excel.

In the first total field: Year 2007 in this example the the Field formua
currently is:
Year3: IIF([Forms]![fExport]![YearBack2]=[Year],Amount,0) and this is
summed for the database grouped by the Category.


Steven

 

[KARL DEWEY]

Sounds like a job for Crosstab query.

John,

Thank you for your response. What I want to do is export a query to excel.
I have a query with Totals. I have 3 years that will be totaled grouped by
Category.
The final will look like for example:
Category | Year 2007 | Year 2008 | Year 2009
group sum sum sum

The issue is that it will not always be 2007 ; 2008 and 2009. That is
where I want to use a function, "or something", to be able to give the Field
alias the correct year for the headings when it exports to excel.

In the first total field: Year 2007 in this example the the Field formua
currently is:
Year3: IIF([Forms]![fExport]![YearBack2]=[Year],Amount,0) and this is
summed for the database grouped by the Category.


Steven

Only by constructing the entire SQL string for the query in code. The query
engine cannot see VBA variables.

The need to do this makes me suspect that your table design might be wrong -
could you explain the context? It would be very rare to have a variable
fieldname!

 

[KARL DEWEY]

Try this changing table name to yours --
PARAMETERS [Forms]![fExport]![YearBack2] Long;
TRANSFORM Sum(TableSteven.[Amount]) AS SumOfAmount
SELECT TableSteven.[Category]
FROM TableSteven
WHERE (((TableSteven.Year) Between [Forms]![fExport]![YearBack2] And
[Forms]![fExport]![YearBack2]-2))
GROUP BY TableSteven.[Category]
PIVOT "Year " & [Year];

Or this which does not require operator input --
TRANSFORM Sum(TableSteven.Amount) AS SumOfAmount
SELECT TableSteven.Category
FROM TableSteven
WHERE (((TableSteven.Year) Between Year(Date()) And Year(Date())-2))
GROUP BY TableSteven.Category
PIVOT "Year " & [Year];

Sounds like a job for Crosstab query.

John,

Thank you for your response. What I want to do is export a query to excel.
I have a query with Totals. I have 3 years that will be totaled grouped by
Category.
The final will look like for example:
Category | Year 2007 | Year 2008 | Year 2009
group sum sum sum

The issue is that it will not always be 2007 ; 2008 and 2009. That is
where I want to use a function, "or something", to be able to give the Field
alias the correct year for the headings when it exports to excel.

In the first total field: Year 2007 in this example the the Field formua
currently is:
Year3: IIF([Forms]![fExport]![YearBack2]=[Year],Amount,0) and this is
summed for the database grouped by the Category.


Steven

Is it possible to use a function to return a variable to be the Field Name in
a query.

Only by constructing the entire SQL string for the query in code. The query
engine cannot see VBA variables.

The need to do this makes me suspect that your table design might be wrong -
could you explain the context? It would be very rare to have a variable
fieldname!

 

[John W. Vinson]

John,

Thank you for your response. What I want to do is export a query to excel.
I have a query with Totals. I have 3 years that will be totaled grouped by
Category.
The final will look like for example:
Category | Year 2007 | Year 2008 | Year 2009
group sum sum sum

The issue is that it will not always be 2007 ; 2008 and 2009. That is
where I want to use a function, "or something", to be able to give the Field
alias the correct year for the headings when it exports to excel.

In the first total field: Year 2007 in this example the the Field formua
currently is:
Year3: IIF([Forms]![fExport]![YearBack2]=[Year],Amount,0) and this is
summed for the database grouped by the Category.

It souns to me like a Crosstab query using [Year] as the column header would
get you what you want, but I have no idea how your table is structured.