Top N values using textbox

[ChoonBoy]

How do I use a textbox to decide on the top number of records in a query? Eg.
If I want Top 12, I will enter 12 into the textbox and so on.. Click a button
and the query will open with the Top 12 customers.

Below is my Select SQL statement. I want the Top 10 to be controlled by a
textbox.

SELECT TOP 10 qryTblOrders3.custcode, qryTblOrders3.CustName,
Sum(qryTblOrders3.Sales) AS Top10Sales
FROM qryTblOrders3
GROUP BY qryTblOrders3.custcode, qryTblOrders3.CustName
ORDER BY Sum(qryTblOrders3.Sales) DESC;

Any help appreciated. Thanks in advance

 

[tkelley via AccessMonster.com]

Will this work?

Dim strSQL as string
Dim intTopValues as integer

strSQL = "SELECT TOP " & intTopValue & " qryTblOrders3.custcode,
qryTblOrders3.CustName,
Sum(qryTblOrders3.Sales) AS Top10Sales
FROM qryTblOrders3
GROUP BY qryTblOrders3.custcode, qryTblOrders3.CustName
ORDER BY Sum(qryTblOrders3.Sales) DESC;"

me.recordsource=strSQL
or
me.listbox.recordsource=strSQL
or
whatever it is you are wanting to fill with this recordsource.

 

[tkelley via AccessMonster.com]

I left out a line:

after:
Dim intTopValues as integer

intTopValues = me.txtTopValues

Will this work?

Dim strSQL as string
Dim intTopValues as integer

strSQL = "SELECT TOP " & intTopValue & " qryTblOrders3.custcode,
qryTblOrders3.CustName,
Sum(qryTblOrders3.Sales) AS Top10Sales
FROM qryTblOrders3
GROUP BY qryTblOrders3.custcode, qryTblOrders3.CustName
ORDER BY Sum(qryTblOrders3.Sales) DESC;"

me.recordsource=strSQL
or
me.listbox.recordsource=strSQL
or
whatever it is you are wanting to fill with this recordsource.

How do I use a textbox to decide on the top number of records in a query? Eg.
If I want Top 12, I will enter 12 into the textbox and so on.. Click a button

[quoted text clipped - 10 lines]

Any help appreciated. Thanks in advance

 

[ChoonBoy]

Thanks it works. initially I did not notice the spelling error in the variable.

I left out a line:

after:
Dim intTopValues as integer

intTopValues = me.txtTopValues

Will this work?

Dim strSQL as string
Dim intTopValues as integer

strSQL = "SELECT TOP " & intTopValue & " qryTblOrders3.custcode,
qryTblOrders3.CustName,
Sum(qryTblOrders3.Sales) AS Top10Sales
FROM qryTblOrders3
GROUP BY qryTblOrders3.custcode, qryTblOrders3.CustName
ORDER BY Sum(qryTblOrders3.Sales) DESC;"

me.recordsource=strSQL
or
me.listbox.recordsource=strSQL
or
whatever it is you are wanting to fill with this recordsource.

How do I use a textbox to decide on the top number of records in a query? Eg.
If I want Top 12, I will enter 12 into the textbox and so on.. Click a button

[quoted text clipped - 10 lines]

Any help appreciated. Thanks in advance