asked on

How to create or change a query in code for a report

In code, I need to change the wording in the data source query in a report. The Report's "built-in" query specifies a range of dates for the Transaction Date field. Can I just create a new SELECT clause and assign it to a string variable and use this in the Filter section of a DoCmd.OpenReport command, thus...

DoCmd.OpenReport "Transactions Report", acViewPreview, NewStringVariable, "HHID = " & ClientNowPrinting

...or do I need to create the query and use the query name in the DoCmd statement?

The text of the query is so long, I wish I could use code to just change the criteria for one field in the design mode some way.

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David_W_R

ASKER

Thanks for the input. I won't get to test it until tomorrow. By "change the SQL property" I believe you mean edit the (very complex) query and re-attach it to the Report.

So if I instead use a Where clause in the open statement, does it then return a subset of the original Report criteria (like a filter applied to a filtered dataset) ?

ASKER CERTIFIED SOLUTION