Dates in a Crosstab Query

[CCS8DENI]

I have a cross tab query that totals the money spent by clients in a business over a period of time.<br><br>I have tried to include a feature that allows you to select the spending history for a certain period of time in the simple query I based my crosstab query on but it now return an error when I&nbsp;&nbsp;try to access the report attached to the crosstab query<br><br>Cheers <p>Andrew<br><a href=mailto:andrew.mcmillan@det.nsw.edu.au>andrew.mcmillan@det.nsw.edu.au</a><br><a href= > </a><br>Technology Adviser<br>
Dept. of Education and Training<br>
Deniliquin District<br>
New South Wales<br>
Australia

 

[elizabeth]

That's not a very specific stmt of the problem or error, but I'll try this one on you... I use a calendar control to set two date controls (BeginDate & EndDate) on a form, then use those values as selection criteria in the underlying query. You might want to try this. If the problem is that you have no data in the range selected by the client, you might want to restrict possible values to existing ones or do error trapping in the NoSata event to let your user know that is the problem.&nbsp;&nbsp;If the problem is that you have data that is not matching up in the report, it may be that you need a report with dynamic columns instead of the regular static ones. You can tell this by running the underlying query by itself and noting if the query's colum headers match up with the report's. Dynamic reports required VBA coding expertise, I think I've seen a sample in the Microsoft Knowledge Base.

 

[CCS8DENI]

Thanks Elizabeth but I have done a similar thing.<br><br>The begin and end date feature has been included in the simple query(client costs) containing the data my crosstab query(client total) draws from. Whether I include this feature before or after creating the crosstab query(client total) it still brings up the error. The crosstab runs fine with out the begin and end date feature running in the simple query (client costs). <br><br>The aim of this exercise is to be able to put in the date range on the report attched to the crostab query(client total) and print it out<br><br>The only way it works is when I use a make table query and create a cross tab from the new table created. This is fine for me but not the guy using the database <p>Andrew<br><a href=mailto:andrew.mcmillan@det.nsw.edu.au>andrew.mcmillan@det.nsw.edu.au</a><br><a href= > </a><br>Technology Adviser<br>
Dept. of Education and Training<br>
Deniliquin District<br>
New South Wales<br>
Australia

 

[elizabeth]

Again, you're pretty vague. It sounds like you have <br>1. a simple query, <br>2. a crosstab query (based on the first query), <br>3. a report (based on the crosstab query), and <br>4. a form with controls that contain a begin and end date.<br><br>It sounds as if the first step, running the simple query based selection criteria in the underlying form, does not work correctly. But the simple query, crosstab query, and report all work correctly if you don't specify a date range. Is this what is happening? If so, you may not be referring to your selection criteria in the form correctly. In your query, you should have something like: <b>forms!YourFormName!YourControlName</b><br><br>If you need more help, please specify the exact error message, and paste the SQL for the query that is not working here.