dynamic date specific CTquery, static report 1

[GaborH]

I am not sure if this question is really appropriate in this group. It has to do with the mechanics of a cross tab query and associated report. Specifically, I have a cross tab query with columns that are department and month specific. ( Dept.1/June, Dept.2/June, etc.) The associated report is run on a monthly basis, and lists the next three months. If the report is formatted to look for column fields that list June, July and August, then what will happen next month, when the query has data for July, August, and September?

Up to know, I have been using a series of static fields in my queries and forms, where Month00 is current month, Month01 is next, etc. Is there a better way of making my CT query and associated report dynamic?

All hints are highly appreciated!

 

[dhookom]

Here is a link to my generic solution

http://dbforums.com/arch/201/2003/9/907054.

It uses DateDiff("m",,) which may be similar to your existing method. I find it works very well.


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

 

[GaborH]

Thank you dhookum!
I am also trying to total my columns by month, but I have 3 months of department specific columns. The columns are Dept1/June, Dept2/June, June total, Dept1/July, Dept2/July, July total, etc.

Q. Is it possible to have several total columns in a CT query that total on different conditions/ months?

Thank you,

GaborH

 

[dhookom]

If you are looking to create multiple value columns, check faq701-4524.

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