[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-02-19
4
Medium Priority
?
436 Views
Last Modified: 2014-02-20
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
Comment
Question by:David_W_R
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39871740
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
 

Author Comment

by:David_W_R
ID: 39871764
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39871837
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
 

Author Closing Comment

by:David_W_R
ID: 39874652
Thanks for the guidance.  I'm now going to post another question asking what's wrong with my next try
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question