Passing parameter to report

[matthewh86]

Hi,

I can't get a form to open a report and pass in parameters to restrict the selection. I have two controls on frmCycleSprintChart (cmb_From and cmb_To), and button which executes the following to launch the report (rpt_CycleSprintChart):

DoCmd.OpenReport "rpt_CycleSprintChart", acViewPreview, , "StrComp(End_Cycle_Sprint," & Me.cmb_From & ")=0"

Ideally, I need to pass both parameters (from and to) so that I can restrict the range of Cycle/Sprints coming back, instead of getting the whole range. E.g. from CD29-1 to CD29-4 would cover 4 sprints, so 4 stacked bar columns would be visible in the report).

I've tried following a few guides, but haven't been able to do it.

Could anyone take a look? I've attached the database.

 

[matthewh86]

Thanks, although you seem to have disabled playback on other sites, so they don't play from that link you've given.

 

[matthewh86]

No problem. It could be this:
https://productforums.google.com/forum/#!topic/youtube/_SdyaTAWkJ0

I've started watching them on youtube, but it seems to be covering a use case more complicated than mine. The videos also make extensive use of code templates which I assume have been collected and saved by yourself, and is a bit overwhelming for an Access beginner!

Are there any simpler solutions to being able to launch a report from a form filtered/based on form controls?

 

[matthewh86]

I'll have a look at your example database and see if I can apply my method to it.. I'll probably do a YouTube video!

I look forward to the solution

 

[matthewh86]

The End_Cycle_Sprint attribute contains those (Cycle_Sprint) entries.

Each cycle has a number of sprints associated, each sprint is between two dates, and I'm grouping on those sprints so that I can see what kind of work has been accomplished per sprint.

I was able to add a End_Cycle_Sprint WHERE clause in the Query Builder, but don't know how I can specify the value from the form controls.

E.g. >=[frm_Report_CycleSprintChart].[cmb_From] And <=[frm_Report_CycleSprintChart].[cmb_To]

EDIT: I can manually add a WHERE clause into the report chart query builder so that it would return between two sprint values (e.g. CD29-1 and CD29-4), but that's not very flexible.

I've tried following this guide, but it refuses to work (or I've missed something obvious!).
http://www.baldyweb.com/wherecondition.htm

There are a number of other guides which suggest the same way, but it just doesn't seem to work with the query I've created which the report is based upon.

 

[matthewh86]

Which field are you going to apply that criteria against?

End_Cycle_Sprint from the "view_Story_Complete" query, which is used on the report.

 

[JHB]

Try it now, database attached, remember to choose values which are available.

You can't use the WHERE clause in the open report method because the report is unbound.
When you have parameters in a pivot query, you need to "Declare" the "Parameters" in the start of the query, then you can use it in the WHERE clause.

[B][COLOR=Red]PARAMETERS [Forms]![frm_CycleSprintReport]![cmb_From] Text ( 255 ), [Forms]![frm_CycleSprintReport]![cmb_To] Text ( 255 );[/COLOR][/B]
TRANSFORM Sum(view_Story_Complete.Story_Points) AS SumOfStory_Points
SELECT view_Story_Complete.End_Cycle_Sprint
FROM view_Story_Complete
[B][COLOR=Magenta]WHERE (((view_Story_Complete.End_Cycle_Sprint)>=[Forms]![frm_CycleSprintReport]![cmb_From] And (view_Story_Complete.End_Cycle_Sprint)<=[Forms]![frm_CycleSprintReport]![cmb_To]))[/COLOR][/B]
GROUP BY view_Story_Complete.End_Cycle_Sprint
PIVOT view_Story_Complete.Class_of_Work_Desc;

 

[matthewh86]

Try it now, database attached, remember to choose values which are available.

Excellent! Thanks a lot!

 

[JHB]

You're welcome.