Dynamic report?

[Bokazoit]

I have made a query and in that query each month in a year is indicated by a number. SO that:

January = 1
February = 2
etc.

To get the latest month - September I can get retrive the month number this way:

DMax("[måned]";"[CP kat impl]";"[År] = " & DMax("[År]";"[CP kat impl]"))

The above formula will give me a result = 9

To get August I can retrive it with this:

DMax("[måned]";"[CP kat impl]";"[År] = " & DMax("[År]";"[CP kat impl]"))-1

etc.

But I can't seem to make it work in my report?

The report should only show the 3 past month (July, August, september now) and I thought I can make a formula in the report like the above. But it get an error. Any suggestions?

 

[dhookom]

I don't have a clue what your field names mean or the symptoms of "can't seem to make it work".

When you use this as a control source in a text box, you must start the expression with "=".
=DMax("[måned]";"[CP kat impl]";"[År] = " & DMax("[År]";"[CP kat impl]"))-1

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bokazoit]

Yes I know that!

But in my query or cross-tab query each month will be labeled 1 to 12 as I explained above.

So when I make a report and wants to see the revenue of september, I create a textbox and in the field 'Control source' I point at month 9 (=september).

So When we reach october (or month 10) it would be much easier if I could make the report look for past 3 month automatically.

So when I show the formula I use to retrieve the latest month its to show that it can be done, but I need some help to use this (or perhaps not) to make the report show the past 3 month.

So to be precise. I need my report to show the month:

July, august, september (month 7, 8, 9) this month

And next month:

August, september, October (month 8,9,10)

 

[dhookom]

I missed the part about your query being a crosstab.
I just posted a method for creating reports from crosstabs of monthly columns to the FAQs. It might not be accessible immediately so I am listing it here. See if this resolves your issue.
Dynamic Monthly Crosstab Report
Try not to use "absolute" column headings for dates. You could possibly use "relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.