Report from a Crosstab query column heading problem....Very frustrating!!!

[Frasier433]

My report is not working because of the crosstab query column headings. The crosstab query pulls the last three months of data. It labels the column headings as the date. When I update the crosstab query and try to run the report it is looking for column headings that are no longer there (it drops a month each month I run the report). How can I get the report to update automatically to only include the column headings available in the crosstab query instead of looking for the original ones that the report was designed on?

[June7]

This is always an issue when basing reports on crosstab queries. Crosstab headings are often so dynamic, especially when dates are involved. Stabilizing the report to run perpetually without edits can be difficult. I count myself lucky I don't need any reports based on crosstabs.

A VBA procedure could modify and save the report to reflect the changed fieldnames of the crosstab. I have some idea of what the code would be like but have never written or seen any such code.

[Frasier433]

I finally got it. No VBA required. For the column date headings I just put in:

ColHead: "M" & DateDiff("m",[Over/Under Monthly]![Date],Date())

Which relabels my columns to be M0, M1, M2, M3,.....Mx.

Those columns are now dynamic and update automatically so that each month, M0 is the current month, M1 is the previous month, M2 is the previous-previous, etc. This allowed me to run the report and always pull the data from column heading that now never changes.

[June7]

Congratulations on finding solution. After seeing your post I do recall helping someone do something like this, but was quite some time ago. Sorry the idea did not come to top of my head, but I did feel it tickle while I read your original post. However, I bet it feels good to get it yourself!

[Frasier433]

I can't really say that I did it on my own. Just happened to get some inspiration from something I found on google. Thank you for the input!