Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to change a large complex Report query in code

Posted on 2014-02-20
11
Medium Priority
?
383 Views
Last Modified: 2014-02-20
The dates in this query begin as #1/1/2013# And #12/31/2013# and I need to change them with the dates in the text below.

Dim XbyAfrom as Date
Dim XbyAto as Date
Dim qdf As QueryDef

                XbyAfrom = #3/1/2013#
                XbyAto = #8/31/2013#


Set qdf = CurrentDb.QueryDefs("qy_DateToDateTransactionReport")
qdf.Parameters!TradeDate = "Between " & XbyAfrom & " And " & XbyAto
Reports![RpQtr-Transactions131231YTD by account].RecordSource = "qy_DateToDateTransactionReport"
                DoCmd.OpenReport "RpQtr-Transactions131231YTD by account", acViewPreview, "qy_DateToDateTransactionReport", "HHID = " & ClientNowPrinting

This compiles and runs ok, but doesn't work to change the date range.  Am I even close?

Some duplication here, but you get the idea.   What's wrong, please?

All I need to do is replace the dates in a huge query.  Not so simple using all this text in code, because of all the line breaks.  I'm considering just "finding" the dates by using Right and Left instructions and replacing them by manipulating the string.
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
  • 6
  • 5
11 Comments
 
LVL 15

Expert Comment

by:JimFive
ID: 39874744
Where are your dates coming from?  If they are in a form you can use [Forms]![FormName]![FormField] as the query parameter.
0
 

Author Comment

by:David_W_R
ID: 39874755
Yes, they are coming from a form, so...

qdf.Parameters!TradeDate = "Between " & [Forms]![FormName]![txtbox1] & " And " & [Forms]![FormName]![txtbox2] should work?

Why don't the variables work just as well?   They were assigned the values from the same form controls.
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39874764
Probably Not.  TradeDate as a parameter in the actual query probably makes the query look like "...WHERE SomeDate = [TradeDate]"  If that is true you need to change the query to say something like "...WHERE SomeDate BETWEEN [Forms]![FormName]![txtbox1] AND [Forms]![FormName]![txtbox2]  Then you need to do NOTHING in the vba code.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:David_W_R
ID: 39874817
That's simple enough, and viable, but I had intended the Report to be used with or without the Form, so can I just replace the variables in my original code with the Form references when the Form is open?
0
 

Author Comment

by:David_W_R
ID: 39874875
I tried replacing the variables with Form control references, and it does not appear to work.  No errors thrown, but no effect on the Report date range.

qdf.Parameters!TradeDate = "Between " & Forms!fm_ReportPrinter!txtYtoYbyAcctFrom & " And " & Forms!fm_ReportPrinter!txtYtoYbyAcctTo

Does this need some kind of refresh or update command to take effect?
How can I print the parameter after the command is executed?

Debug.print qdf.Parameters!TradeDate    ??
0
 
LVL 15

Accepted Solution

by:
JimFive earned 2000 total points
ID: 39874905
How are you going to get the date range without the form?

Here's what I usually do.  I create the dialog form for the parameters and I create the query to reference that form.  Then, I put code in the Reports OnOpen Event to open the dialog to get the parameters.  On the Dialog form the "Display Report" button hides the dialog but keeps it open which allows the OnOpen event of the report to finish and keeps the form controls available for the query.  The "Cancel" button closes the dialog form.  The Reports OnOpen event checks for the form being open and if it is not then it cancels the report.  Then whenever you try to open the report the dialog pops up asking for the parameters and the form controls are available for the query.
If you want to be able to run the reports automatically you just need to pass the data you want to use to the form using the openarguments parameter and then use the forms OnOpen event to fill in the controls and automatically hide the dialog if openarguments is not null.
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39874920
What does your actual query look like.  You can't set the parameter "TradeDate" = "Between Date1 and Date2" because that is not a parameter.  The parameter needs to be a single date.  The "Between" needs to be in the original query.
0
 

Author Closing Comment

by:David_W_R
ID: 39874928
I like that.   Plenty of meat to digest.  Thanks a bunch!
0
 

Author Comment

by:David_W_R
ID: 39875086
So the Date1 and Date2 need to be separate variables?
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39875120
Yes, Date1 and Date2 need to be separate.
0
 

Author Comment

by:David_W_R
ID: 39875125
Thanks again.   I have my homework assignment for tomorrow.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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