Report parameter query filter issues

I've been able to make some improvements since last posting, but have run into new issues. Here's the background:

I have a single report that I want to run 2 different ways: no filter and with filter. The filter involves a parameter query where the user enters a date range - (((Date) Between [Enter starting date] And [Enter ending date]))).

I have a form with command buttons for each way to run the report, which point to a macro using OpenReport commands. One OpenReport leaves the filter name and where condition blank and the other points to a filter (I've also tried with just typing in the SQL under the where condition). Both reports return the correct numbers.

Here's where it gets tricky...I want to display an additional text box on the report when the filter is applied. Sounds easy, right?

1) Typically to get a parameter query entry to print on a report, you place the parameter query in a text box, right? Well, when I do that in order print the date range entered (and remove the filter from the OnOpen report command), the report tries to apply the parameter query even when the text box property is visible = no.

2) Using the visible property in general seems to be a problem. If I set the text box property to visible = no in the report, and then in the filter's macro place a command after the OpenReport command that is SetValue where I flip the text box property to visible = yes... the report asks the user to input the date range twice. So, the SetValue command triggers the filter again after it's already run on the report's OnOpen. (I should say the text box does not use the parameter query in this one, I was trying it out with just plain text)

Am I going to have to break down and duplicate all of my reports in order to run filter and no filter? Ack!

my advice would be to use vba to launch the report, and change the text box/apply the filter settings.


Replace all 'Report1' with your report Name
Replace the txtStartDate/EndDate with the TextBox names of the start/end date text boxes
everything else should be self explanitory, and you can modify it to be what you want..

Also, you can open the report hidden if you don't want your user to get confused with report popping into window then disappearing then back.

Wow! Thanks for the advice! The client wanted all macros, but I'll go with what works and write it up in the manual later.

One last issue that I can't figure out.

If my "Report1.Text1" (the text box that gets the Visible property toggled in the report) includes the parameter query inside it (which prints beautifully under the filter), how do I get it to ignore this text box when the filter is not applied? The report seems to want to apply the parameters in the text box even when the property visible is no. I've even created similar vb for the no filter option that specifically states no filter and Text1.Visible = False. Do I need to use vb to create the text box when the filter is applied and delete it on no filter? Maybe I'm reaching here... don't get to use vb that often so I'm kinda rusty.

Just so I'm understanding you right-

You want textbox to be invisible by default when filter is not applied? And filter will turn it on and place whatever value in it or somewhat it is, correct?

If that is the case, all you need to do is set textbox's visible property to No in the property sheet. It will then be invisible by default unless explicitly turned on via code, as you would do with filter.

HTH.

Yep. You understand correctly.

Did that. Unfortunately the report still reads the parameter in the text box when the visible property is no in the property sheet and I just open the report in preview (without appying any of the new vb).

Likely this wouldn't be an issue if I was using just plain text, but I would really like the parameter query entry to print when the filter is applied.

Just so I'm understanding you- the code is executed when you open report regardless how you open or only if you click a certain button? If it's the former, it'll read the parameter whether it's visible or not. It shouldn't happen if you open it in other means without executing the code.

HTH.

Yes. It's running no matter what. When I remove the parameter (which mimics the vb code so I can print the entry by the user) from the text box there is no issue. But, again, I'd like it to print when the filter is applied.

I'm now thinking about ways I could suppress the 'Enter parameter value' query to the user in the no filter scenario since clicking 'OK' will return all cases.