Passing parameter to report (1 Viewer)

M

matthewh86

Registered User.
Local time
Today, 00:15
Joined
May 11, 2017
Messages
16
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.
 

Attachments

  • Squad 8 Story Database.zip
    269.4 KB · Views: 86
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:15
Joined
Jul 9, 2003
Messages
16,273
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..
 
M

matthewh86

Registered User.
Local time
Today, 00:15
Joined
May 11, 2017
Messages
16
Thanks, although you seem to have disabled playback on other sites, so they don't play from that link you've given. :)
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:15
Joined
Jul 9, 2003
Messages
16,273
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...
 
M

matthewh86

Registered User.
Local time
Today, 00:15
Joined
May 11, 2017
Messages
16
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

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:15
Joined
Jul 9, 2003
Messages
16,273
matthewh86 said:
Are there any simpler solutions to being able to launch a report from a form filtered/based on form controls?
Click to expand...


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!
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:15
Joined
Jul 9, 2003
Messages
16,273
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.


Code: 
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;
 
M

matthewh86

Registered User.
Local time
Today, 00:15
Joined
May 11, 2017
Messages
16
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.
 
Last edited:
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:15
Joined
Jul 9, 2003
Messages
16,273
matthewh86 said:
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.
Click to expand...

Which field are you going to apply that criteria against?
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:15
Joined
Jul 9, 2003
Messages
16,273
Put the following in the "view_Story_Complete" query

Code: 
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?
 
J

JHB

Have been here a while
Local time
Today, 01:15
Joined
Jun 17, 2012
Messages
7,732
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.
Code: 
[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;
 

Attachments

  • Squad 8 Story Database1.zip
    143.3 KB · Views: 85
You must log in or register to reply here.

Similar threads

C
Solved Passing values from form to report
Replies
5
Views
419
ClaraBarton
C
L
Solved Saving a report to a specified folder
Replies
3
Views
222
Gasman
Gasman
A
Use VBA to change Report parameter. DoCmd.SetParameter
Replies
11
Views
633
anymuon
A
I
Passing values to a report
Replies
4
Views
452
plog
P
C
How to refer to form using a variable?
Replies
6
Views
270
cricketbird
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom