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.

 

[Uncle Gizmo]

I have found the simplest way to tackle this problem is not to try and pass parameters to the report but have the report grab the parameters from a form. You can see how this is done in my blog here:-

Videos - ‎Generate Many Invoices in MS Access‎


which also contains a video explanation and a Google presentation of the method I use..

 

[matthewh86]

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

 

[Uncle Gizmo]

Thanks for the heads up Matthew.

I opened my website in an incognito window and sure enough I couldn't watch the videos! However if you click on the video and go through to YouTube, you CAN watch the videos!! It's very odd behaviour... I'd be interested to know if you can watch them on YouTube, that might well help me track down what is going on...

 

[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?

 

[Uncle Gizmo]

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

Thank you very much for the heads up and the solution Matthew, brilliant!!!

 

[Uncle Gizmo]

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

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!

 

[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

 

[Uncle Gizmo]

I'm not sure we are anywhere near a solution yet! It appears that I am misunderstanding your question or there is something missing.

From the way you have constructed your form for selecting an item named something like "CD15-3" for a start Point, and again selecting a similar item for the Endpoint, then I would expect to see those values present in your chart SQL statement. However if you examine the SQL statement (below), there doesn't appear to be any reference to the selected start and end points? Please could you explain.

TRANSFORM Sum(view_Story_Complete.Story_Points) AS SumOfStory_Points
SELECT view_Story_Complete.End_Cycle_Sprint
FROM view_Story_Complete
GROUP BY view_Story_Complete.End_Cycle_Sprint
PIVOT view_Story_Complete.Class_of_Work_Desc;

 

[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.

 

[Uncle Gizmo]

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.

Which field are you going to apply that criteria against?

 

[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.

 

[Uncle Gizmo]

Put the following in the "view_Story_Complete" query

SELECT view_Story.ID, Switch(view_Story.[FT_Number] Is Not Null,"FT-" & view_Story.[FT_Number] & " - " & [tbl_Feature].[Description],view_Story.Class_of_Work_Desc="Project Bug","Misc Project Bugs",view_Story.Class_of_Work_Desc="Support Bug","Support Bugs",True,"Misc") AS Work_Details, Switch(view_Story.Task_No Is Not Null,"US-" & view_Story.User_Story_ID & "-" & view_Story.Task_No,view_Story.User_Story_ID Is Not Null,"US-" & view_Story.User_Story_ID,True,Null) AS User_Story_Task, Switch(view_Story.Bugzilla_ID Is Not Null,"BZ" & view_Story.Bugzilla_ID,view_Story.QSM_ID Is Not Null,"QSM" & view_Story.QSM_ID,view_Story.ServiceNow_ID Is Not Null,view_Story.ServiceNow_ID,True,Null) AS Bug_ID, view_Story.tbl_Story.Description, view_Story.Class_of_Work, view_Story.Class_of_Work_Desc, view_Story.Story_Size_Desc, view_Story.Story_Points, view_Story.SRA_Required, view_Story.Planned, view_Story.Raised_Date, view_Story.Raised_Cycle_No, view_Story.Raised_Cycle_Sprint, view_Story.Start_Date, view_Story.Start_Cycle_No, view_Story.Start_Cycle_Sprint, view_Story.End_Date, view_Story.End_Cycle_No, view_Story.End_Cycle_Sprint, view_Story.Comments
FROM view_Story
WHERE (((view_Story.End_Cycle_Sprint) Between "CD18-1" And "CD18-3") AND ((view_Story.Status_Desc)="Complete"));

Is that the sort of result you would expect?

 

[Uncle Gizmo]

Now for the obligatory video
Between CD18-1 And CD18-3 - Nifty Access

 

[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.