David_W_R
asked on
How to change a large complex Report query in code
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_Da teToDateTr ansactionR eport")
qdf.Parameters!TradeDate = "Between " & XbyAfrom & " And " & XbyAto
Reports![RpQtr-Transaction s131231YTD by account].RecordSource = "qy_DateToDateTransactionR eport"
DoCmd.OpenReport "RpQtr-Transactions131231Y TD by account", acViewPreview, "qy_DateToDateTransactionR eport", "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.
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_Da
qdf.Parameters!TradeDate = "Between " & XbyAfrom & " And " & XbyAto
Reports![RpQtr-Transaction
DoCmd.OpenReport "RpQtr-Transactions131231Y
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.
Where are your dates coming from? If they are in a form you can use [Forms]![FormName]![FormFi eld] as the query parameter.
ASKER
Yes, they are coming from a form, so...
qdf.Parameters!TradeDate = "Between " & [Forms]![FormName]![txtbox 1] & " And " & [Forms]![FormName]![txtbox 2] should work?
Why don't the variables work just as well? They were assigned the values from the same form controls.
qdf.Parameters!TradeDate = "Between " & [Forms]![FormName]![txtbox
Why don't the variables work just as well? They were assigned the values from the same form controls.
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]![txtbox 1] AND [Forms]![FormName]![txtbox 2] Then you need to do NOTHING in the vba code.
ASKER
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?
ASKER
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!txt YtoYbyAcct From & " And " & Forms!fm_ReportPrinter!txt YtoYbyAcct To
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 ??
qdf.Parameters!TradeDate = "Between " & Forms!fm_ReportPrinter!txt
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 ??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
I like that. Plenty of meat to digest. Thanks a bunch!
ASKER
So the Date1 and Date2 need to be separate variables?
Yes, Date1 and Date2 need to be separate.
ASKER
Thanks again. I have my homework assignment for tomorrow.