Report Errors when running Crosstab Query with Parameter

[Paul Howarth]

I posted this question....

http://bytes.com/topic/access/answers/946707-crosstab-queries-responsible-access-error-msg

...and was provided with the following support link which completely resolved the error message I was getting when running the Query:

http://support.microsoft.com/?id=209778

Now that the Query is running just fine, I have a new problem when running the Report which uses the Query.

The Query prompts for [StartDate] and [EndDate] ONCE (as it should).
On the other hand, the Report which uses the Query prompts for [StartDate] and [EndDate] up to FOUR TIMES. The dumb workaround is to press the Enter Key a few times before entering [StartDate} and[EndDate].

But even worse, when I try editing the Report in Design View, I am prompted for [StartDate] nearly every time I move the cursor anywhere within the Report - making it nearly impossible to edit the Design of the Report.

Help!

[Seth Schrock]

Please post the SQL for your query after reading Before Posting (VBA or SQL) Code paying particular attention to the formatting example found at the bottom of the post and be sure to use the code tags (they can be entered automatically using the [CODE/] button).

I have never heard of that happening when in the design view of the report. What version of Access are you using?

[Paul Howarth]

Report Errors when running Crosstab Query w Parameter

Please post the SQL for your query after reading Before Posting (VBA or SQL) Code paying particular attention to the formatting example found at the bottom of the post and be sure to use the code tags (they can be entered automatically using the [CODE/] button).

I have never heard of that happening when in the design view of the report. What version of Access are you using?

Hopefully I am replying to your request for add'l info in the correct way....

I am using Access 2010 from Office 2010. My issue in the design view of the report has never happened to me in any of the dozens of reports I have created - including reports that rely on crosstab queries.

Hopefully I am about to insert the SQL code from the Queries properly.
qryBillingRepor t1 prompts for [StartDate] and [EndDate] once

PARAMETERS 
   StartDate DateTime, 
   EndDate DateTime;
SELECT 
   IIf(IsNull([Lot Books].[OrderDate]),
         Null,
         [Lot Books].[ClientRef]) 
      AS TotalOrdered, 
   IIf(IsNull([Lot Books].[CancelDate]),
         Null,
         [Lot Books].[ClientRef])
       AS Cancelled, 
   IIf(IsNull([Cancelled]),
         [Lot Books].[ClientRef],
         Null) 
      AS NetOrdered, 
   IIf(IsNull([Lot Books].[InvoiceDate]),
         [Lot Books].[LB_ID],
         Null) 
      AS NotDelivered, 
   [Lot Books].LB_ID, 
   [Lot Books].OrderDate, 
   [Lot Books].CancelDate, 
   [Lot Books].ClientREF, 
   [Lot Books].Client, 
   [Lot Books].InvoiceDate, 
   IIf(([Lot Books].[InvoiceDate]>10/1/1958),
         [Lot Books].[LB_ID],
         Null) 
   AS Delivered
FROM [Lot Books]
WHERE ((([Lot Books].OrderDate) 
         Between [StartDate] 
            And [EndDate]) 
         AND (
            ([Lot Books].Client)<>"AMG"));

qryBillingRepor tALL prompts for [StartDate] and [EndDate] twice

SELECT 
   qryBillingReport1.TotalOrdered, 
   qryBillingReport1.Cancelled, 
   qryBillingReport1.NetOrdered, 
   qryBillingReport1.NotDelivered, 
   qryBillingReport1.OrderDate, 
   qryBillingReport1.Delivered, 
   qryBillingReport1.ClientREF, 
   qryBillingReport1.InvoiceDate, 
   IIf(([Unpaid]=0),
         0,
         Date()-[Lot Books].[OrderDate]) 
      AS DaysUnpaid, 
   [Invoice101]+[Invoice131] 
      AS Invoice, 
   IIf(IsNull([qryBR_Crosstab]![101]),
         0,
         [qryBR_Crosstab]![101]) 
      AS Invoice101, 
   IIf(IsNull([qryBR_Crosstab]![131]),
         0,
         [qryBR_Crosstab]![131]) 
      AS Invoice131, 
   IIf(IsNull([qryBR_Crosstab]![201]),
         0,
         [qryBR_Crosstab]![201]) 
      AS Received, 
   [Cancel]+[Adjust] 
      AS CancelAdjust, 
   IIf(IsNull([qryBR_Crosstab]![211]),
         0,
         [qryBR_Crosstab]![211]) 
      AS WriteOff, 
   [Invoice]+[Received]+[CancelAdjust]+[WriteOff] 
      AS Unpaid, 
   qryBR_Crosstab.[101], 
   qryBR_Crosstab.[131], 
   qryBR_Crosstab.[201], 
   qryBR_Crosstab.[206], 
   qryBR_Crosstab.[211], 
   qryBR_Crosstab.[216], 
   IIf(IsNull([qryBR_Crosstab]![206]),
         0,
         [qryBR_Crosstab]![206]) 
      AS Cancel, 
   IIf(IsNull([qryBR_Crosstab]![216]),
         0,
         [qryBR_Crosstab]![216]) 
      AS Adjust, 
   IIf(([Unpaid]=0 And [Cancelled] Is Null),
         1,0) AS PaidFull
FROM qryBillingReport1 
      INNER JOIN qryBR_Crosstab 
         ON qryBillingReport1.ClientREF = qryBR_Crosstab.ClientREF;

The Report which uses qryBillingRepor tAll prompts for [StartDate] and [EndDate] three times.

I will also insert a picture of the Report in Design View showing the prompt for [StartDate] which repeatedly appears anywhere I move the cursor in the Report in design View.
[imgnothumb]http://bytes.com/attachments/attachment/6912d1361488347/capture.jpg[/imgnothumb]

[Seth Schrock]

Try creating a form with two text boxes; one for the start date and one for the end date. Then in your query, instead of using your Between [StartDate] and [EndDate], reference the controls on your form. The syntax for the reference is Forms!form_name!control_name. You will need to edit your parameters as well.

This is mostly guessing on my part as I've only used crosstab queries once, but I didn't have a report based on it. I will have a look around the web to see if I can find something else.

PS... From what I've been able to find on the web, your situation is why you shouldn't use parameter prompt crosstab queries. The solution was to use a form to provide the values as I described above. Not sure if this will fix the problem of having it prompt you while in design view or not.

[zmbd]

Here's another thought...

I think that the reason the SQL in the second block is prompting you so often is that the CrossTab is out of scope at each invocation - I'm guessing here!

Now when I've used Parameters within a normal select, I only need to enter them the one time on run and then they appear to be global to the remaining SQL much as if you use the form that Seth has proposed.

With that in mind, what I suggest is that you make a copy of your Query, then altered the copy by adding a PARAMETERS clause to the outer SELECT query with the same names and data types as you have in the CrossTab.

-=I make no promises that this will work as I've never tried to do this with a Select query that had a parameter based subquery.

SO, PLEASE let post back with what happens.

PARAMETERS 
   startdate DateTime, 
   enddate DateTime;
SELECT  
   qryBillingReport1.TotalOrdered,  
   qryBillingReport1.Cancelled,  
   qryBillingReport1.NetOrdered,  
   qryBillingReport1.NotDelivered,  
   qryBillingReport1.OrderDate,  
   qryBillingReport1.Delivered,  
   qryBillingReport1.ClientREF,  
   qryBillingReport1.InvoiceDate,  
   IIf(([Unpaid]=0), 
         0, 
         Date()-[Lot Books].[OrderDate])  
      AS DaysUnpaid,  
   [Invoice101]+[Invoice131]  
      AS Invoice,  
(<<<Remaining SQL Ommitted>>>)