Top Values - Code

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

What code would I use for an unbound drop-down box with
value list (like 5;5%;10;10%, etc) for user to select top
value of query? Right now, have to make a separate query
(using Access 97 built-in query properties with top value)
for each value...lots of queries! Want user to be able to
select the value and fire off just the one query...

Thanks very much. Mike
 
AFAIK, you will need to construct the SQL String in code using the value of
the ComoboBox. You can then modify the SQL String of the saved Query using
the QueryDef object.
 
Howard, I had tried this as my first thought but couldn't
get it to work. Say I want to get the Top 5 highest
grades (field)...I would use form to drop down "5" from
value list and then use "<=[forms]![main]![top]" in the
criteria portion of the grades field. This just gives me
the ones that made <= a grade of 5.

But how do I get the top 5 or top 5%? Do I have to use an
aggregate function? Thanks again...

Mike
-----Original Message-----
Assume you are running the query from a form (frmReports)
which has the ComboBox (cboTopValues). In the query, in
the Criteria line for the appropriate field, put

"<=[Forms]![frmReports]![cboTopValue]"

This will run it and use whatever the current selected
value is. The only downside is you'll get a prompt if you
run the query and the form is not open or if there isn't
anything selected in the ComboBox.

Hope this helps!

Howard Brody

-----Original Message-----
What code would I use for an unbound drop-down box with
value list (like 5;5%;10;10%, etc) for user to select top
value of query? Right now, have to make a separate query
(using Access 97 built-in query properties with top value)
for each value...lots of queries! Want user to be able to
select the value and fire off just the one query...

Thanks very much. Mike
.
Click to expand...
.
Click to expand...
 
OK...will read the help on this. Not a programmer so not
sure I can follow completely but I thought the answer was
probably in using the control name in SQL string (but
couldn't get it to work by just changing the SQL).

Thanks...
 
"You can't use a control [Forms]![frmReports]![cboTopValue] to replace
the 5 in this context."

I thought that this was essentially Mike's question and that why I suggested
to construct the SQL String in VBA code.
 
Back
Top