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

Question

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.

score 2 · Accepted Answer · edited May 23 '17 at 11:53

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.

Build your query "qUnionTop" with sample values for TOP n.
Copy the SQL to a table where you store the template SQL.
Edit the SQL with variables, e.g.
SELECT TOP $c1$ col1 FROM tblx UNION SELECT TOP $c2$ col1 FROM TBLY UNION ...
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.

Ok, so I should use VBA (that's a bummer). Can you guide me a little bit? In my Form I have 4 textboxes (kat1, kat2, kat3, kat4), so user can put in 4 values which later go into query as: `SELECT TOP kat1.value col1 FROM tblx UNION SELECT TOP kat2.value col1 FROM TBLY UNION etc` Is it a good idea to create a VBA script that will go to the n-th place in query (place after TOP and before collumn names) and replace it with TextBox value? And then repeat this 4 times with different n values? — EpicKris, Nov 15 '16 at 09:45
Thank you for your help. I avoided bulding another query by just placing some placeholders in my SQL that later are replaced with your VBA code with some tweaks. — EpicKris, Nov 15 '16 at 10:52

score 0 · Answer 2 · edited Sep 29 '18 at 02:32

On the form properties go to the event tab. On the on load event you should create an Event Procedure looking like the following:

Option Compare Database

Private Sub Command0_Click()
Dim kat As String

kat = "SELECT TOP " +Me.Kat1.Value + "TableName.TableField "


CurrentDb.QueryDefs("QueryName") .SQL = kat
On Error Resume Nest
DoDmd.RunQuery "QueryName"
End Sub

Private Sub Form_Load()
End Sub

Private Sub testrun()
End Sub

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

2 Answers2