[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

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.
0
David_W_R
Asked:
David_W_R
  • 2
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can supply a WHERE clause to the OpenReport method:

DoCmd.OpenReport "YourReport", acViewPreview, , "Date1=#01-01-2014#"

But your query will return only those dates as specified by the "range of dates for the Transaction Date" field.

If you need to change that, you can change the SQL property of the QueryDef:

Dim qdf As DAO.QueryDef
Set qdf = Currentdb.QueryDefs("YourReportQuery")

qdf.SQL = "Your SQL Here"

But you'd have to include the full, valid SQL query syntax.
0
 
David_W_RAuthor Commented:
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) ?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
By "change the SQL property" I believe you mean edit the (very complex) query and re-attach it to the Report.
Essentially, yes. You'd change the SQL of that query via code by setting the .SQL property as shown, and then run your report (which would use the new SQL of the query).
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) ?
Yes. If your original query returns all Dates > 01-01-2013, and you then filter for Dates < 12-30-2012, your report would return no records.
0
 
David_W_RAuthor Commented:
Thanks for the guidance.  I'm now going to post another question asking what's wrong with my next try
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now