How does one pass parameters to subreports? 1

[huggybear]

Please forgive me if this has been answered previously but the keyword search feature was down when I came looking!

I need to have my subreports filtered, some with dates, others with other criteria. I have a dialog box for users to enter their criteria. How do I get the subreports to respond to their appropriate criteria?

Thanks,

Bear

 

[JoeMiller]

The queries that supply the subreport the data should also reference the dialog boxes that the main report query does. Joe Miller
joe.miller@flotech.net

 

[huggybear]

Thanks, I see how that works. But now I need to use an expression to evaluate the value of a checkbox to determine whether fields with a certain value of their own are included.

I have tried using the IIF function as in:
IIF(BoxIsChecked, CriteriaIsSuchAndSuch, DifferentCriteria) but nothing comes through to the report. Does the IIF function not work from the query grid? Or is there a better way to create an equivalent function?

Thanks

Bear

 

[JoeMiller]

In the criteria of the query you can use AND/OR to pull varying data and multiple criteria.. Can you give me a more specific example, IE: query names and field names?

Joe Miller
joe.miller@flotech.net

 

[huggybear]

I sure can. But first, let me say that by using your previous suggestion I am now able to supply starting and ending dates for a couple of the subreports. Thanks for that.

I have a database with an Issues table. In that table there is a field called Status. Status can be Waiting, Started, Completed or Null.

What I'm trying to do with the subreport related to that table is display only those Issues that have a status that is not Completed -- unless the user decides to see them all.

So, in the criteria cell I used the IIF function like this:

IIF(chkCompleted, Like *, <>&quot;Completed&quot; OR Is Null)

That gives me nothing as a result. If I just use the <>&quot;Completed&quot; OR Is Null by itself as the criteria, it works.

So, how do I build the expression that uses the Check Box?

Thanks again,
Bear

 

[JoeMiller]

You can't use the IIf function to do this because IIF returns a String so when you use it the query criteria is translated by Jet as '<>&quot;Completed&quot; OR Is Null' in quotes so it's looking for a value of '<>&quot;Completed&quot; OR Is Null' in the field. Instead you'll need to make a QueryDef which is where you have a query &quot;already saved&quot; and then modify it's definition when you execute some code (like when the users presses the button that opens the query). The code will look at the checkbox, see if it's checked and then modify the query on the fly.

Another way to do this, which is a little different, is you could get rid of the checkbox and make a combobox holding the parameters for the criteria. So the user could select &quot;ALL&quot; or &quot;Incomplete&quot; and the bound column could have your criteria selections (&quot;Like *&quot; and &quot;<>'Completed' OR Is Null&quot. This is the way I would do it, then you don't have to write the code to look at the checkbox. You only have to do the QueryDef and you can just reference the Combo in your code for the criteria parameters.

HTH
Joe Miller
joe.miller@flotech.net

 

[huggybear]

OK, thanks. I've not used a querydef before so I'll have to bone up on the process.

I'll be back if I can't make it work, but not until after the weekend. We're going camping in the rain.

Bear

 

[huggybear]

Joe, I have used a querydef as you suggested and it works as advertised. Thank you.

Bear