Hi

I was wondering if anybody has some ideas. i need to create a report that has three years of history. it is hard to describe the entire project, but i will describe it breifly.

the user enters a date range (usually would be jan 1 until some date within the same year). i then want to show the data for that date range in the first column, and then the past two years in the next two columns. the problem is that the underlying query is different for each of those columns. to be a bit more specific, for year 0 i need to split the data based on the date range and in year -1 and year -2 i need to have a different query per year.

i would love to write all this in VBA, however, a report needs to have be bounded with one and only one recordset. i know that in DAO i was able to create at runtime a querydef that would maybe help. however i use only ADO. this last part i am not sure makes a difference. basically what would be optimal for me would be to some how attach a recordset to each column. anybody saw anything resembling this, i would be happy if you share it.

thanks,

sam

You can do this with a single query. Use a form to capture the date range because you're going to have to write some code to calculate the other two ranges.

The where clause for the query will look something like:
Where SomeDate Between Forms!YourForm!Range1Start AND Forms!YourForm!Range1End OR SomeDate Between Forms!YourForm!Range2Start AND Forms!YourForm!Range2End OR SomeDate Between Forms!YourForm!Range3Start AND Forms!YourForm!Range3End;

You also need to add a calculated field to segregate the ranges.

Select ...., IIf(SomeDate Between Forms!YourForm!Range1Start AND Forms!YourForm!Range1End, "Range1", IIf(SomeDate Between Forms!YourForm!Range2Start AND Forms!YourForm!Range2End, "Range2", "Range3")) AS RangeName

And finally, after you get that query working and saved, use the crosstab wizard to create the final query. Use the calculated RangeName field as the column heading.

Thanks for your reply. It definetly opened my eyes to something i haven't thaught about. However, this is my situation.

each date range must split up date with an if statement. basically my data is not based by day it is by payperiod, which means i need to split up a span of two weeks and take out the fractional time elapsed. so lets say the parameters are 1/1/xx - 12/31/xx i need to take all which happened 12/19/xx-1 until 1/13/xx+1 and then have a multiplier which multiplies some columns. this query runs for more than a minute.

therefore i am trying to get three queries going in VBA so that it is easier to set up. otherwise it may take me a long time to set up and then each time its run. i don't think that if it takes five minutes it would bother me, i just hope i wouldn't have a problem timing out and debugging is also very difficult.

I will give your solution a try, but if you have another solution i would appreciate if you can let me know.

thanks,

sam

Three queries would not be more efficient than one query. Most of the time taken by a query is selecting the records. By using a single query, Access can select all the records it needs in a single pass of your table. By using three queries, you would require Access to read your tables three times instead of once.

You need to write the code to calculate the three date ranges and then the single query can use the three separate date ranges to select all the data at once.

thanks a lot. i am now reworking my queries and consilidating them a bit. however, i think that it would be nice to add multiple recordsets to a report. (obviously not in a subreport)

sam