Date Function as Field Name

[Kirsti]

Hi,

Is it possible to adjust the SQL in the query to change the field name to a date function?

Basically, I want: Format(DateAdd("m",-1,Now()),"mmmm") to be my field name.

Thanks, Kirsti

[June7]

Not in an Access query object, tried this before, field names are not dynamic, cannot be set with an expression. What you will see is the expression string as the field name. Try it.

[Kirsti]

Yes, I have tried it and that's exactly what I found. I was just hoping for some work-around that someone knew of! That's ok, I'll figure out some other way of getting my data to present the way I want it to.

Thanks.

[pbaldy]

I think you'd have to build the SQL of the query in VBA code to be able to concatenate that in as the field name.

My daughter just got back from NZ; they loved it!

[Kirsti]

Thanks Paul - I'll give that a go.

Glad to hear your daughter loved NZ - it's a beautiful place (earthquakes aside)!

[June7]

Yes, VBA can build the SQL statement then can use to set RecordSource of form/report but the bound controls will no longer have a valid field reference. However, if you are setting RowSource of a graph object, might work.

[Kirsti]

Ok, thanks June. Will give it a try.

[Kirsti]

Ok, being a total dummy when it comes to VBA, I didn't get this working, however I found a work around though creating a new query, as all I really wanted was for the series name in my chart to show the name of the previous month, and I managed to do that within the data rather than changing the field name, so I have marked this thread as closed.

Thanks again for your help.