Passing value from the textbox to “TOP n” clause in SQL query

I believe I have quite simple question, that would help me finish my project. I don’t usually work with Access, but I was asked to help someone so here I am.

My problem: I have a Form1 called “Start” in which there is a TextBox1 called “Kat1”. I also have a SQL Query as:

SELECT TOP 3 tbl.Example FROM TABLE TBL

What I want to achieve is to let the user to write some number in “Kat1” so that Query returns this much top rows.

I hope there is a way to this without using VBA, since my query is rather complicated, there are more textboxes, more subqueries with selecting top rows etc.

I tried putting SELECT TOP [Start]![Kat1]!Value or simmilar. There maybe something wrong with my syntax or maybe this is all wrong and there is another way. Thank for help in advance.

Edit: For future readers 😉 This is how I solved it with VBA:

Sub Query_Change()

SQLstring = "SELECT TOP KAT1 col1 FROM TBL UNION SELECT TOP KAT2 col1 FROM TBL etc..."`

CurrentDb.QueryDefs("MyQuery").SQL = SQLstring

For i = 1 To 4
    SQLstring = Replace(SQLstring, "KAT" & i, Forms!Start!("Kat" & i).Value)
Next i

CurrentDb.QueryDefs("MyQuery").SQL = SQLstring
End Sub

The code will run after user puts values into TextBoxes.

Answer

This is not possible in a Access query, the “TOP n” part cannot take a variable / parameter.

(It’s possible in SQL Server, see Dynamic SELECT TOP @var In SQL Server )

You need VBA to do it. Either, since it’s in the very first part of a SELECT statement, read the original Querydef.SQL and edit it (replace the 3rd “word”).

Or have a table with template SQL code, with e.g.

SELECT TOP $count$ FROM table WHERE ...

Replace $count$ with your number and write the result to a querydef.

Edit

In this case, I would definitely go the template route.

1. Build your query “qUnionTop” with sample values for TOP n.
2. Copy the SQL to a table where you store the template SQL.
3. Edit the SQL with variables, e.g.
   SELECT TOP $c1$ col1 FROM tblx UNION SELECT TOP $c2$ col1 FROM TBLY UNION ...
4. Before you open your query run code like this:

Sub DynamicQueryFromTemplate()

    Dim S As String
    Dim i As Long

    ' Read template SELECT SQL from tblTemplates
    S = DLookup("Sql", "tblTemplates", "Key = 'qUnionTop'")
    ' Replace $c[x]$
    For i = 1 To 4
        S = Replace(S, "$c" & i & "$", Forms!Start("Kat" & i).Value)
    Next i

    CurrentDb.QueryDefs("qUnionTop").SQL = S

End Sub

Whenever your query needs to change, repeat steps 2.+3.