Using a Variable Name in the Field name of the query

[M Thompson]

I have a query used to produce a report that breaks out
information by fiscal quarters. Right now I have to go
through and update the query by hand to change the field
names every quarter. I'm trying to figure out a way to
automate the process to automatically change all the names
and date ranges based on the current date. Any
suggestions?

 

[Kent Prokopy]

Yes you can create a Variable Field Name:

PARAMETERS [MyValue] Short;
SELECT Field1, IIf([MyValue]=1,[Field2],[Field3]) AS Expr1
FROM Table1

When you run this query it will prompt you for the value of [MyValue]. If
you enter the number 1 it will return the data from field2. If you enter the
number 2 it will return the data from field3.

Hope this helps...

 

[John Vinson]

I have a query used to produce a report that breaks out
information by fiscal quarters. Right now I have to go
through and update the query by hand to change the field
names every quarter. I'm trying to figure out a way to
automate the process to automatically change all the names
and date ranges based on the current date. Any
suggestions?

I'd suggest you look at your table structure! It appears that you're
storing data - such as the fiscal quarter - in a fieldname. What are
these fieldnames? How is your table structured? Might you not do
better to have a tall-thin table structure with the quarter or the
date *AS DATA*, rather than in a fieldname?