Report is Blank!

[GoBrowns!]

I need help with a report. Here is what the SQL looks like:

TRANSFORM Count(MRPC_Query.Reason_Code) AS CountOfReason_Code
SELECT MRPC_Query.MRPC
FROM MRPC_Query
GROUP BY MRPC_Query.MRPC
PIVOT MRPC_Query.Reason_Code;


The idea is to have the user input a beginning and end date into a form,
then click the button to run this report. I want it to show up as a bar graph
that shows the user for a given time period, how many of each reason code was
entered for items in each of the MRP Controllers. I have tried the report
multiple ways, but the report keeps coming up blank. What am I doing wrong?!?

I created the report in Wizard, but I thought seeing the SQL would help. Any
ideas on how to fix?!?

 

[Duane Hookom]

I don't see where the user in inputting anything. You must enter the data
types of all parameters used in crosstab queries. Also, if you are using
parameter prompts, I think this is a fumble. Always use controls on forms for
all user interface.

Does your MRPC_Query return records? What is its SQL view?

 

[GoBrowns!]

There is a form that asks the user for a Beginning and End date. The user
inputs the dates, clicks on the command button, and the report is supposed to
open. It works on my other report, just not this one....

Here is the SQL for my Query "MRPC_Query":

SELECT DISTINCT Conformance_to_Schedule_Archive.Actual_Finish_Date,
Conformance_to_Schedule_Archive.Work_Center,
Conformance_to_Schedule_Archive.Work_Center_Description,
Conformance_to_Schedule_Archive.Material,
Conformance_to_Schedule_Archive.Material_Description,
Conformance_to_Schedule_Archive.Schedule_Quantity,
Conformance_to_Schedule_Archive.Adjusted_Quantity,
Conformance_to_Schedule_Archive.Remaining_Quantity,
Conformance_to_Schedule_Archive.Percentage_Complete,
Conformance_to_Schedule_Archive.Reason_Code,
Conformance_to_Schedule_Archive.[Reason_Code Description], MRPC.MRPC
FROM Conformance_to_Schedule_Archive LEFT JOIN MRPC ON
Conformance_to_Schedule_Archive.Material = MRPC.IDH
WHERE (((Conformance_to_Schedule_Archive.Actual_Finish_Date) Between
[Forms]![Reason_Code_Report_Form]![Beginning Date] And
[Forms]![Reason_Code_Report_Form]![Ending Date]));


I can get the query to return values, just not the report. Then I thought it
may be the type of report I am trying: I want a bar graph that graphs the
various reason codes for each MRPC based on the frequency of reason code, if
that makes sense.

Thanks for the help!!!!!!!!

 

[Duane Hookom]

As I stated previously "You must enter the data types of all parameters used
in crosstab queries". While in design view of either your select or crosstab
query menu: Query->Parameters and enter:
[Forms]![Reason_Code_Report_Form]![Beginning Date] Datetime
[Forms]![Reason_Code_Report_Form]![Ending Date] Datetime

See if that makes any difference.
--
Duane Hookom
Microsoft Access MVP

There is a form that asks the user for a Beginning and End date. The user
inputs the dates, clicks on the command button, and the report is supposed to
open. It works on my other report, just not this one....

Here is the SQL for my Query "MRPC_Query":

SELECT DISTINCT Conformance_to_Schedule_Archive.Actual_Finish_Date,
Conformance_to_Schedule_Archive.Work_Center,
Conformance_to_Schedule_Archive.Work_Center_Description,
Conformance_to_Schedule_Archive.Material,
Conformance_to_Schedule_Archive.Material_Description,
Conformance_to_Schedule_Archive.Schedule_Quantity,
Conformance_to_Schedule_Archive.Adjusted_Quantity,
Conformance_to_Schedule_Archive.Remaining_Quantity,
Conformance_to_Schedule_Archive.Percentage_Complete,
Conformance_to_Schedule_Archive.Reason_Code,
Conformance_to_Schedule_Archive.[Reason_Code Description], MRPC.MRPC
FROM Conformance_to_Schedule_Archive LEFT JOIN MRPC ON
Conformance_to_Schedule_Archive.Material = MRPC.IDH
WHERE (((Conformance_to_Schedule_Archive.Actual_Finish_Date) Between
[Forms]![Reason_Code_Report_Form]![Beginning Date] And
[Forms]![Reason_Code_Report_Form]![Ending Date]));


I can get the query to return values, just not the report. Then I thought it
may be the type of report I am trying: I want a bar graph that graphs the
various reason codes for each MRPC based on the frequency of reason code, if
that makes sense.

Thanks for the help!!!!!!!!

I don't see where the user in inputting anything. You must enter the data
types of all parameters used in crosstab queries. Also, if you are using
parameter prompts, I think this is a fumble. Always use controls on forms for
all user interface.

Does your MRPC_Query return records? What is its SQL view?