Show All Columns from params in a Crosstab Query 2

[mirirom]

heya,

i have a crosstab query that prompts for 2 parameters: beginningDate and endingDate. obviously, the query returns results for data related to the entered range, starting with the first range value with data. i'm wondering, however, if it's possible to show ALL of the columns, regardless of whether or not there's related data.

for example, a user enters 5/01/01 and 12/01/01. since data only exists for months 7, 8, & 10, the dynaset returns the following column headers: 7/01, 8/01, 9/01, 10/01.

is there a way to have the query show all the months in the range as column headers? any advice would be greatly appreciated. thanks

mirirom

 

[MichaelRed]

Generate a recordset with the dates in the range as a column. Join this recordset to the Crosstab query (Left Join) on hte dates with the generated recordset being the outter.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[mirirom]

hi Mike,

thanks for the tip. worked fine, although creating outer joins in access is no fun...

mirirom

 

[MichaelRed]

If you use the the query builder it is easy.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over