Report based on parameter query

  • Thread starter Thread starter Kent Prokopy
  • Start date Start date
K

Kent Prokopy

Within vba I am trying to use the:
Application.Docmd.OutPutTo acOutPutReport "ReportName", acFormatRTF,
"C:\Report.rtf"

The query this report is based on takes parameters:
ContractID
Start Date
End Date

When I execute the OutPutTo it prompts for the input values. Can this be
passed to the query or report prior to execution?

Thank you
Kent Prokopy
 
If you haven't already built a form to enter the parameter
data in, you'll have to do so. You can then link the
fields on the form to the table(s)(or not) and then link
the form fields to the prebuilt query. The query below
links to some fields on a form. The parameters list points
to all the fields on the form and then in the query body
you pass the form data. (The syntax is odd, no brackets in
the parameter list but brackets in the query so be very
careful.) Hope this helps.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 255 ), Forms!
MultiSiteMrB!txtCust Text ( 255 ), Forms!MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short, Forms!
MultiSiteMrB!txtTimePrd Text ( 255 ), Forms!MultiSiteMrB!
txtStrPrd Short, Forms!MultiSiteMrB!txtEndPrd Short, Forms!
MultiSiteMrB!txtStrLoc Text ( 255 ), Forms!MultiSiteMrB!
txtEndLoc Text ( 255 );

TRANSFORM Sum(Totalpounds.TotWgt) AS Wgt

SELECT Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart("yyyy",[SHIPDATE]) AS Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
AS Period

FROM Totalpounds

WHERE (((UCase([Totalpounds].[Vnd]))=UCase([Forms]!
[MultiSiteMrB]![txtVend])) AND ((UCase(Left([Totalpounds].
[Cust],5)))=UCase(Left([Forms]![MultiSiteMrB]!
[txtCust],5))) AND ((DatePart("yyyy",[SHIPDATE])) Between
[Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) AND ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between [Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]![MultiSiteMrB]!
[txtEndPrd]) AND ((UCase(Left([Totalpounds].[Loc],5)))
Between UCase(Left([Forms]![MultiSiteMrB]![txtStrLoc],5))
And UCase(Left([Forms]![MultiSiteMrB]![txtEndLoc],5))))

GROUP BY Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])

ORDER BY Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])

PIVOT Left([Totalpounds].[Loc],9);
 
Back
Top