Re: SQL TOP question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You cannot provide a number for TOP as a parameter at runtime.

Therefore you will need to generate the SQL statement dynamically.

Assuming you have created a query named Query1 that contains the right syntax, switch it to SQL View. Copy the SQL statement into a string in your code. Break it into 2 strings where the number needs to go. You can then patch in a number, and assign it to the SQL property of the query, or directly to the RecordSource of a form (or report in Report_Open.)

This kind of thing:
Dim strSql As String
Const strcStub = "SELECT * FROM Table1 WHERE Table1.ID IN (SELECT TOP "
Const strcTail = " ID FROM Table1 AS Dupe WHERE ... );"

strSql = strcStub & "5" & strcTail
CurrentDb.QueryDefs("Query1").SQL = strSql
'Forms!Form1.RecordSourse = strSql

I assume you were referrring to this example:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sophie" <Sophie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E8F90161-14B3-488C-A3B3-7695D1C6318A@xxxxxxxxxxxxxxxx
I was quite interested in some SQL I saw earlier in this site from Allen
Browne and John Spencer. If the poster is OK with this, I'd like to steal it
for my app that deals with ping-pong tounaments. The SQL involved
SELECT........ (SELECT Top 3 ....) ...

Here is what I'm trying to do. when we have tournaments with only a handful
of games, we base the team award on the top 3 scores. But if there are many
teams and many games in a large touney, I'll base the team award on the top 4
or even 5 scores. Instead of determining the team award by hand as I now do,
I'd like my scoring app to have a combox with a value list of 3,4,5. Here
is my question - how do I chose 4 from the combox, for example, then get the
SQL to return Top 4, not Top 3?

Thanks to the contributors of this site
--
Thanks
Sophie

.

Relevant Pages

  • Re: SQL string problem
    ... clean up your parameters before sending them to SQL. ... > You must "escape" any single quotes when sending SQL statements ... > Just pass your SQL statement through this function when opening your ... >> Here is the full SQL statement that is assigned to the strSQL ...
    (microsoft.public.excel.programming)
  • Re: SQL string problem
    ... You must "escape" any single quotes when sending SQL statements directly to ... Just pass your SQL statement through this function when opening your ... > Here is the full SQL statement that is assigned to the strSQL ...
    (microsoft.public.excel.programming)
  • Re: Run-time error 3709
    ... Hi Fox ... dim strSQL as string ... CTRL-G to Goto the debuG window -- look at the SQL statement ...
    (microsoft.public.access.modulesdaovba)
  • Re: Problem updating memo field with string data from form
    ... Im just wondering if the syntax of the Update section of the SQL ... the SQL statement ... Docmd.RunSQL strSQL ... Bob Quintal ...
    (comp.databases.ms-access)
  • Re: SQL statement is truncated.
    ... When you concatenating string for the SQL ... > tried the strSQL as a variant and as a string. ... > along with this Excel. ...
    (microsoft.public.data.ado)