Field/coloumn headings in reports

[Epona]

Hi all,

I have a report that is generated from a query that gives me the current month as a date and then the subsequent months rolling forward as M1, M2, M3, M4 etc

ie if current month is 2012/2/1 then M1 must be Feb, M2 Marc etc



My question is;
How do I get the field names/headings of the columns to change dynamically every time the report is run on a new month. I have tried date add from th month field no luck.

Any other ideas?

[aytee111]

Use the format function, e.g. Format(datefield, "mmm yyyy") would give you the month name. Don't forget to use the year otherwise your data will be put together year after year.

[Epona]

Hi Aytee111,
That does not work as the data is not in the field name it is in a seperate coloumn so it needs to reference the date from that coloum, the field names themselves m1 etc remain constant irrespective of what the date in the date coloum is.

My logic is (simplified)

Find date in Field.Date
Return in M1 Field.Date +1 Format Date mm yyy. M2 would be +2 etc

Does that make sense? the question is how

[RayMilhon]

You would use Format(DateAdd("m",0,datefield),"mmm yyyy") then subsuquent months simply change the 0 to 1, 2, 3 ....

[Epona]

But how do I get it to look for the actual date in another field if it is not in the current field name?

Have tried to attach a view of the report it currently is - Maybe it will make more sense

[Epona]

maybe this will work

[RayMilhon]

First make sure the headings are textboxes not labels. Second in the controlsource property put in the format command as stated previously. based on your sample the datefield would be [mnth]

[Epona]

Thanks RayMilhon,
I will try that and get back to you.

[Epona]

I am getting an "enter parameter" value prompt. Must be missing something

Fixed - I forgot the []

[Epona]

Where on earth is the extra bracket?

[RayMilhon]

can you post the entire SQL Statement?

[Epona]

Format(DateAdd("m",0,[Mnth]),"mmm yyyy") in the control source box as you suggested.

If I remove the [] around the Mnth then I get teh parameter error but not the ( error

Am I missing something?

[RayMilhon]

The [] specifies to access it's a fieldname If ther is no field with that name in the query Access assumes a parameter. So Is Mnth the field name. That's why I asked for the SQL

[Epona]

Yes 'Mnth' is the field name for the field that the month/date to be selected resides in.

I am not sure I understand what you are looking for when you ask for the sql code? If it is different to the one I posted previously then can you please advise me where to find it.

Thanks

[RayMilhon]

Took another look is Format(DateAdd("m",0,[Mnth]),"mmm yyyy") preceded by an = if not add it. Le me know