"TOP n" -- setting "n" by query/form 1

[cp950]

I need to pull the "TOP n" of a table, based on simple criteria (like Top 3 Salarys of employees, where Department = X). I know I can set "n" easily in design-mode, but this I need to run by form by a user who won't know Access. The "n" will always change (Top 5, Top 11, Top 2, etc.)

I can easily collect that number in a form.

But can I run a query from code or macro, telling it what "n" is?

I did try to set "Top Values" to "Forms!Form1!Text1", but the query will only accept a number or an acceptable constant.

Can anybody help? Thanks in advance!

 

[SgtJarrow]

You will need to create the SQL statement on the fly....something like:

strSQL = "SELECT TOP " & Forms![frmForm]![Control] & " tblData.ProcessDate, tblData.Value, tblData.text
FROM tblData;"

Then set this a s the recordsource for the form or report to display....


****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[cp950]

OK, thank you VERY much!

And if I needed to use this data in subsequent queries, I could make this query a Make-Table type, and off I go. Right?

 

[SgtJarrow]

Sure.....just convert the query to a make-table format and you should be good...

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com