Query 1 has a list of projects and hourly allocations, restricted to the "TOP 10" hourly allocations.
Query 2 uses the results from Query 1 to restrict a list of projects, resources, and hourly allocations of each. It wouldn't work to restrict Query 2, because it has more than 10 results... as it is, the TOP factor is working just the way I want it to.
I have a report based upon Query 2, and my users call this report from a form.
What I want to know is, is it possible to have the user select the report from the form, and then be given a list of choices for the TOP factor?
For example, the user picks "Report TOPS blah blah" from the form, another form pops up with a listbox saying, "Pick one of the following options:
Top 5 Values
Top 10 Values
Top 25 Values
From which they pick one, hit a button, and then the report shows up.
I know the mechanics of offering them the choice, I just don't know how to plug the resulting change into the SQL for Query 1. Obviously the Top Values box isn't accepting "=Forms![Form1]![topnum]" or anything like that...
Are you running the query using sql? I think that this is the only way to do what you want. My SQL is pretty weak so I make the query using Access and view the SQL. Then I copy and paste the SQL into my VBA. You'll need to use a string as a variable to change your range. (ex. strRange = "TOP 5"). So your sql will look something like this. strSql = "SELECT" & strRange & "myTable.* ...." . Now make this the row source of your report and your done.