Report based on a parameter query how to make it run

[ManuelLavesa]

Hello all,


I have a form that collects the parameters for a query, then the form has a button that runs the query and shows the results. So far this works, but now I would like to get the results into a report. I made a report based on the parameter query, but once I collect the parameters on the form and click the button to open the report, the report shows new text boxes for the parameters again. And the parameters on the form do not work. So my question is how can I make a Report based on the parameter query accepting the values from the parameter form? Do I need to run the query first and then send it to a report... I would appreciate any ideas on how to make the parameter query result into a report... thank you

[June7]

Don't run the query. Set the report RecordSource property to the query. Open report.

I never use dynamic parameterized queries. I prefer to use VBA to construct filter criteria and apply to report when it opens. Review http://www.allenbrowne.com/ser-62.html

VBA to open report would be like:

DoCmd.OpenReport "report name", , , strWhere

[ManuelLavesa]

Thank you June7. The method you suggested works very well for everything except for the date range. My code was as follows:

 If Not IsNull(Me.txtStartDate) Then
                        strWhere = strWhere & "([DateOrdered] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
                    End If
                        If Not IsNull(Me.txtEndDate) Then
                        strWhere = strWhere & "([DateOrdered] >= " & Format(Me.txtEndDate , conJetDate) & ") AND "
                        End If

The code works, the report opens but there is no data when I should be getting all the records... The same report opens with the data when I ask for Customer, or Product instead of date range. Am I missing something? Thank you for your help.

[June7]

My code isn't exactly like the example. I don't use Format and conJetDate. I just use # delimiter when constructing my date criteria, however, my system date and user inputs are Access/U.S. standard of mm/dd/yyyy.

This works for me:
"([DateOrdered] >= #" & Me.txtEndDate & "#) AND "

Regardless, Allen's code is well-known and proven. It should work.

[ManuelLavesa]

Thank you June7. It works great now... The problem were the signs in the end date I had them >= instead of < . Insn't that funny... Yes you are right the code from Allen works as well. Thank you for your help.

[June7]

Ooops, I should have seen that. Glad it's working now.