top N value

[GEORGIA]

Is there a way to make TOP N value as a parameter query? Meaning, I want to
have the parameter box pop up when i run this query and put in whatever
number i want. For example, if i want top 25, just type in 25 or 10 or 5
....
I want to eventually create a form to where user can input their own top N
value to pull. Thanks in advance.

[Roger Carlson]

No. You'll have to create and save the query in VB code.

On my website (www.rogersaccesslibrary.com), there is a small Access
database sample called "CreateQueries2.mdb" which illustrates how to do
this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"GEORGIA" <GEO...@discussions.microsoft.com> wrote in message
news:B44151CE-4B8B-4FC5...@microsoft.com...

[Arvin Meyer [MVP]]

As Roger suggests, you need to do it in code by supplying the Top value as a
variable (in this case n):

Sub cboTopValues_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim n As Integer
Dim strSQL as string

Set db = CurrentDb

n = Val(Me![cboTopValues].Text)

strSQL = "SELECT TOP " & n & " Qty FROM MyTable ORDER BY Qty DESC;"

Set rst = db.OpenRecordset(strSQL)

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

"GEORGIA" <GEO...@discussions.microsoft.com> wrote in message
news:B44151CE-4B8B-4FC5...@microsoft.com...

[GEORGIA]

Thank you so much! works perfectly!!

[GEORGIA]

ok.. i thought it was working..but it's not. :-)

how do i get to show the result?
it won't bring up the result.
here's my code:

Private Sub cboTopValues_AfterUpdate()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim n As Integer

Dim strsql As String

Set db = CurrentDb
n = Val(Me![cbotopvalues].Text)

strsql = "SELECT TOP " & n & " [181+_total] from qryaginsummarytopN order
by [181+_total] desc;"

Set rst = db.OpenRecordset(strsql)
End Sub

thank you

[Roger Carlson]

Hi Georgia,

What Arvin showed you was how to create then open a recordset in code. It
IS working, but you're not doing anything with the recordset after you've
created it. If you actually want to create and execute the query in the
Access GUI, you have to do something just a little different. You have to
create and save the query and then execute it. Something like this:
'-----------------------------

Private Sub cboTopValues_AfterUpdate()
Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim n As Integer
Dim strsql As String

Set db = CurrentDb
n = Val(Me![cbotopvalues].Text)

strsql = "SELECT TOP " & n & " [181+_total] from qryaginsummarytopN order
by [181+_total] desc;"

'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strsql)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_cmdRunQuery_Click
End If
End Sub
'-----------------------------
Notes: The query it creates is called qryMyQuery. If you want it named
something else, change it in the code. The error trapping is for the case
where the query does not exist. If you try to delete a query that doesn't
exist, you'll get an error.

BTW, the sample I mentioned illustrates this technique and many variations
on it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"GEORGIA" <GEO...@discussions.microsoft.com> wrote in message

news:91C26296-F9FF-4896...@microsoft.com...

[GEORGIA]

GREAT!! Works perfectly!
THANK YOU ALL!