2 probs with Access 2000 Report (using crosstab)

[debbiecarvery]

Hello,
I have a report that runs from a crosstab query the crosstab is built from a regular query. Here are my 2 problems.

1. There are 6 heading in my crosstab that I need to show on my report. As long is there is information in those fields I have no problems. If for any reason no data is entered in one of those fields my report will not run. I get an error that reads something like "Field name does not exist".The actual field is text but calculated as a number for example the report calculates how many customer cancelled on a particular day and puts a number under the heading "Cancel", problem is if no customers cancelled that day, my report can not be ran.

2. Date range. I can put a date range in my original query that promts a parameter. I would like for this parameter to promt directly from the report. I am not sure where to put this criteria in the original query ? My crosstab query ? or directly in the report. Not sure what to enter either.

This is my first time building a report based on query and a crosstab query so please be gentle with me.

Thanks a whole lot,
Debbie

 

[JeremyNYC]

Debbie,

Do you have the field names listed explicity in the crosstab query? In the properties of (I forget which, but it's probably...) the column field, which you get to by right clicking on it in design view, you can enter a comma delimited list of field names. This ensures that you'll get those fields/columns even if there is no data. I'm not sure that will do the trick for your report, but it might.

Also, liberal use of the nz() function may do the trick, if you do it in the original query.

On the second question, I'm not too sure. If the prompt in the query works when you just open the query, I would think that it would work when the query is called by the report. But I don't use parameter queries, so I couldn't really say.

Hope this helps.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.