If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
TOP in query user selects amount to return
Using Top, is there a way to ask the user how many records to return? if not
is there another simple way of acheiving this? |
#2
|
|||
|
|||
TOP in query user selects amount to return
Just put the number you want after Top
SELECT TOP 10 ..... "Kevin" wrote: Using Top, is there a way to ask the user how many records to return? if not is there another simple way of acheiving this? |
#3
|
|||
|
|||
TOP in query user selects amount to return
Forget my last post, I have just realised you want the user to supply how
many records to return. You could try building the SQL string and incorporating a textbox from a form as the amount to return e.g. strSQL = "SELECT TOP " & Forms![FormName]![TextBoxName] & " etc etc "Kevin" wrote: Using Top, is there a way to ask the user how many records to return? if not is there another simple way of acheiving this? |
#4
|
|||
|
|||
TOP in query user selects amount to return
Thanks Dennis,
I am sure this will work, and can use it if I need to but, do you know if there is a way to do this from within the query rather than through a form? "Dennis" wrote: Forget my last post, I have just realised you want the user to supply how many records to return. You could try building the SQL string and incorporating a textbox from a form as the amount to return e.g. strSQL = "SELECT TOP " & Forms![FormName]![TextBoxName] & " etc etc "Kevin" wrote: Using Top, is there a way to ask the user how many records to return? if not is there another simple way of acheiving this? |
#5
|
|||
|
|||
TOP in query user selects amount to return
I don't think so. I tried putting this into the SQL view of a query but it
errors out SELECT TOP [Enter How Many] etc. etc. "Kevin" wrote: Thanks Dennis, I am sure this will work, and can use it if I need to but, do you know if there is a way to do this from within the query rather than through a form? "Dennis" wrote: Forget my last post, I have just realised you want the user to supply how many records to return. You could try building the SQL string and incorporating a textbox from a form as the amount to return e.g. strSQL = "SELECT TOP " & Forms![FormName]![TextBoxName] & " etc etc "Kevin" wrote: Using Top, is there a way to ask the user how many records to return? if not is there another simple way of acheiving this? |
#6
|
|||
|
|||
TOP in query user selects amount to return
Thanks Dennis,
I tried that also but, like you, just get errors! "Dennis" wrote: I don't think so. I tried putting this into the SQL view of a query but it errors out SELECT TOP [Enter How Many] etc. etc. "Kevin" wrote: Thanks Dennis, I am sure this will work, and can use it if I need to but, do you know if there is a way to do this from within the query rather than through a form? "Dennis" wrote: Forget my last post, I have just realised you want the user to supply how many records to return. You could try building the SQL string and incorporating a textbox from a form as the amount to return e.g. strSQL = "SELECT TOP " & Forms![FormName]![TextBoxName] & " etc etc "Kevin" wrote: Using Top, is there a way to ask the user how many records to return? if not is there another simple way of acheiving this? |
#7
|
|||
|
|||
TOP in query user selects amount to return
Select Top N records where N is variable ( a parameter)
One method that can be used is to use a ranking query to assign a number to the records and then use the ranking to return N records. 'Probably not updatable SELECT MyTable.* FROM MyTable INNER JOIN ( SELECT A.MyField, Count(B.MyField) as TheCount FROM MyTable as A LEFT JOIN MyTable As B ON A.MyField B.MyField GROUP BY A.MyField ) as Ranking ON MyTable.MyField = Ranking.MyField WHERE Ranking.TheCount [Top How Many Number] 'Probably updatable SELECT MyTable.* FROM MyTable WHERE MyField in (SELECT A.MyField FROM MyTable as A LEFT JOIN MyTable As B ON A.MyField B.MyField GROUP BY A.MyField HAVING Count(B.MyField) [Top How Many Number]) John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County Kevin wrote: Thanks Dennis, I tried that also but, like you, just get errors! "Dennis" wrote: I don't think so. I tried putting this into the SQL view of a query but it errors out SELECT TOP [Enter How Many] etc. etc. "Kevin" wrote: Thanks Dennis, I am sure this will work, and can use it if I need to but, do you know if there is a way to do this from within the query rather than through a form? "Dennis" wrote: Forget my last post, I have just realised you want the user to supply how many records to return. You could try building the SQL string and incorporating a textbox from a form as the amount to return e.g. strSQL = "SELECT TOP " & Forms![FormName]![TextBoxName] & " etc etc "Kevin" wrote: Using Top, is there a way to ask the user how many records to return? if not is there another simple way of acheiving this? |
Thread Tools | |
Display Modes | |
|
|