Naming a field based on a value from another field?

[kiwijules0505]

Hi all,

Is it possible to name a field (heading) based on the value of another field?

At the moment the code looks someting like this:

Sum(IIf([START_DATE]<=[CurrentMonth],IIf([END_DATE]>=
[CurrentMonth],[COST_PER_UNIT],0),0)) AS [ThisMonth Rate]

I would like to name the field based on the value of another field in the same table instead of the static [ThisMonth Rate]

Thanks again
Jules

 

[Pat Hartman]

There is no way to do this with a query. It doesn't make any sense in relational terms due to the potential for assigning multiple names to the same column.

If you want to do something similar on a form or report, that can be done. In a form, you would put the code in the Current event and change the Caption of the control. In a form, the code would go in the Format event of the secton that contains the control.

 

[kiwijules0505]

Thanks for your response, Pat.

What you say does make sense, even though the field will always have the same value in that particular table (but the value will change month on month)

A bit more info:
I'm running 2 queries, the first to list all bills for the pervious period. The user is prompted to enter the bill period when running the first query, e.g. 200710 for Oct-07. The billperiod field in the table produced will therefore contain "200710" for all records.

The second query looks at all customers listed in the table produced and looks up, from a Tariff Table, what their rate for the previous period ("200710") and next period ("200711") is.

I want the headings for the 200710 and 200711 rates to be exactly that, but currently they are static "This Month" and "Next Month"

Maybe I can capture the billperiod when the user enters it in the first query and re-use it later on? As you can probably tell, I'm reasonably new to Access....

Thanks for your help so far.
Jules

 

[Pat Hartman]

You can do this with a crosstab query. Format the Transaction date field as text and use that as the column header.

Select Format(TranDate,"yyyymm") As TextTranDate, .....