Can an expression be used to create a query output field alias?

[TheresAlwaysAWay]

I've been asked to create a report that displays the performance of a group of customers over the last 12 months, broken down by month so trends are visible.

The first immediate problem I face is how to create the query's output field names. I want 13 columns in the result. The first column's name would be the CustomerName, and then assuming it's now Sept of 2018, I'd like the remaining column names to be:

Oct 2017, Nov 2017, Dec 2017, Jan 2018...Sep 2018

I don't think I'll have much problem separating the data into the appropriate groups and generating the numbers we wish to see (if I do I can always start another thread), but I'm puzzling over whether it's possible to use an expression to create the output field name. Obviously next month the first field should be Nov 2017 and the last Oct 2018. I'm trying to generate these names on the fly each time the query is run based on today's date. I know how to create aliases in their normal fixed usage, of course, but this would require a variable that could be evaluated and that would change from month to month.

Does anyone have any creative solutions?

As always, all assistance is greatly appreciated.

 

[TheresAlwaysAWay]

I have solved my own problem.

I was looking at this problem incorrectly. I didn't need to name the query output fields using an expression. I only needed to create the datasheet output with the correct captions.

I created a query using the normal alias naming convention with field names ranging from -11 to 0. -11 is 11 months ago, 0 is current month. Then I populated the query with the correct data that I wanted to see.

I then created a form based on that query with the 13 fields of interest as controls. In the OnOpen event of the form I created a simple macro that renamed the captions for each control label using the following expression.

Format(DateAdd("m",-11,Date()),"mmm yyyy") Currently that returns "Oct 2017". I then changed the -11 to -10 to get "Nov 2017", etc.

Now my column headings on the datasheet view of the form are what I want them to be, regenerated based on the current date each time the form is opened. The data itself sits happily in the query fields named -11, -10, etc., and everything displays exactly what I wanted to see.

As my name says, there's always a way! Thanks to all who read and pondered this.