By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,526 Members | 2,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,526 IT Pros & Developers. It's quick & easy.

Report Errors when running Crosstab Query with Parameter

P: 22
I posted this question....

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

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.

Feb 21 '13 #1
Share this Question
Share on Google+
4 Replies

Seth Schrock
Expert 2.5K+
P: 2,937
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?
Feb 21 '13 #2

P: 22
@Seth Schrock
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.
qryBillingReport1 prompts for [StartDate] and [EndDate] once

Expand|Select|Wrap|Line Numbers
  2.    StartDate DateTime, 
  3.    EndDate DateTime;
  4. SELECT 
  5.    IIf(IsNull([Lot Books].[OrderDate]),
  6.          Null,
  7.          [Lot Books].[ClientRef]) 
  8.       AS TotalOrdered, 
  9.    IIf(IsNull([Lot Books].[CancelDate]),
  10.          Null,
  11.          [Lot Books].[ClientRef])
  12.        AS Cancelled, 
  13.    IIf(IsNull([Cancelled]),
  14.          [Lot Books].[ClientRef],
  15.          Null) 
  16.       AS NetOrdered, 
  17.    IIf(IsNull([Lot Books].[InvoiceDate]),
  18.          [Lot Books].[LB_ID],
  19.          Null) 
  20.       AS NotDelivered, 
  21.    [Lot Books].LB_ID, 
  22.    [Lot Books].OrderDate, 
  23.    [Lot Books].CancelDate, 
  24.    [Lot Books].ClientREF, 
  25.    [Lot Books].Client, 
  26.    [Lot Books].InvoiceDate, 
  27.    IIf(([Lot Books].[InvoiceDate]>10/1/1958),
  28.          [Lot Books].[LB_ID],
  29.          Null) 
  30.    AS Delivered
  31. FROM [Lot Books]
  32. WHERE ((([Lot Books].OrderDate) 
  33.          Between [StartDate] 
  34.             And [EndDate]) 
  35.          AND (
  36.             ([Lot Books].Client)<>"AMG"));
qryBillingReportALL prompts for [StartDate] and [EndDate] twice

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    qryBillingReport1.TotalOrdered, 
  3.    qryBillingReport1.Cancelled, 
  4.    qryBillingReport1.NetOrdered, 
  5.    qryBillingReport1.NotDelivered, 
  6.    qryBillingReport1.OrderDate, 
  7.    qryBillingReport1.Delivered, 
  8.    qryBillingReport1.ClientREF, 
  9.    qryBillingReport1.InvoiceDate, 
  10.    IIf(([Unpaid]=0),
  11.          0,
  12.          Date()-[Lot Books].[OrderDate]) 
  13.       AS DaysUnpaid, 
  14.    [Invoice101]+[Invoice131] 
  15.       AS Invoice, 
  16.    IIf(IsNull([qryBR_Crosstab]![101]),
  17.          0,
  18.          [qryBR_Crosstab]![101]) 
  19.       AS Invoice101, 
  20.    IIf(IsNull([qryBR_Crosstab]![131]),
  21.          0,
  22.          [qryBR_Crosstab]![131]) 
  23.       AS Invoice131, 
  24.    IIf(IsNull([qryBR_Crosstab]![201]),
  25.          0,
  26.          [qryBR_Crosstab]![201]) 
  27.       AS Received, 
  28.    [Cancel]+[Adjust] 
  29.       AS CancelAdjust, 
  30.    IIf(IsNull([qryBR_Crosstab]![211]),
  31.          0,
  32.          [qryBR_Crosstab]![211]) 
  33.       AS WriteOff, 
  34.    [Invoice]+[Received]+[CancelAdjust]+[WriteOff] 
  35.       AS Unpaid, 
  36.    qryBR_Crosstab.[101], 
  37.    qryBR_Crosstab.[131], 
  38.    qryBR_Crosstab.[201], 
  39.    qryBR_Crosstab.[206], 
  40.    qryBR_Crosstab.[211], 
  41.    qryBR_Crosstab.[216], 
  42.    IIf(IsNull([qryBR_Crosstab]![206]),
  43.          0,
  44.          [qryBR_Crosstab]![206]) 
  45.       AS Cancel, 
  46.    IIf(IsNull([qryBR_Crosstab]![216]),
  47.          0,
  48.          [qryBR_Crosstab]![216]) 
  49.       AS Adjust, 
  50.    IIf(([Unpaid]=0 And [Cancelled] Is Null),
  51.          1,0) AS PaidFull
  52. FROM qryBillingReport1 
  53.       INNER JOIN qryBR_Crosstab 
  54.          ON qryBillingReport1.ClientREF = qryBR_Crosstab.ClientREF;
The Report which uses qryBillingReportAll 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.

Attached Images
File Type: jpg Capture.jpg (98.4 KB, 664 views)
Feb 21 '13 #3

Seth Schrock
Expert 2.5K+
P: 2,937
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.
Feb 22 '13 #4

Expert Mod 5K+
P: 5,397
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.

Expand|Select|Wrap|Line Numbers
  2.    startdate DateTime, 
  3.    enddate DateTime;
  4. SELECT  
  5.    qryBillingReport1.TotalOrdered,  
  6.    qryBillingReport1.Cancelled,  
  7.    qryBillingReport1.NetOrdered,  
  8.    qryBillingReport1.NotDelivered,  
  9.    qryBillingReport1.OrderDate,  
  10.    qryBillingReport1.Delivered,  
  11.    qryBillingReport1.ClientREF,  
  12.    qryBillingReport1.InvoiceDate,  
  13.    IIf(([Unpaid]=0), 
  14.          0, 
  15.          Date()-[Lot Books].[OrderDate])  
  16.       AS DaysUnpaid,  
  17.    [Invoice101]+[Invoice131]  
  18.       AS Invoice,  
  19. (<<<Remaining SQL Ommitted>>>)
Feb 22 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.