TOP Records in SQL Statement

[Wayne Aprato]

I've played with this for several hours trying to make it work with no
success. How do I replace "TOP 4" in the following SQL statement with
the input from a textbox on a form?
If possible I want to do this in the query itself - not an SQL
statement in code.

SELECT TOP 4 [SurName], [FirstName], [Company]
FROM RandCont
ORDER BY Rnd([Num]);

I need "TOP 4" to be replaced with something like
"Forms!MyForm!MyTextBox" so that I can specify how many records I want
in the textbox.

Any help would be appreciated.

[Wayne Morgan]

It doesn't appear that it will accept a parameter for this value. Since you
are doing this on a form, you could rewrite the SQL of the query.

CurrentDb.QueryDefs("MyQuery").SQL = "SELECT TOP " & Me.txtMyTextbox &
"[SurName], [FirstName], [Company] FROM RandCont ORDER BY Rnd([Num]);"

--
Wayne Morgan
MS Access MVP
"Wayne Aprato" <cq*******@volcanomail.com> wrote in message
news:4d**************************@posting.google.c om...

I've played with this for several hours trying to make it work with no
success. How do I replace "TOP 4" in the following SQL statement with
the input from a textbox on a form?
If possible I want to do this in the query itself - not an SQL
statement in code.

SELECT TOP 4 [SurName], [FirstName], [Company]
FROM RandCont
ORDER BY Rnd([Num]);

I need "TOP 4" to be replaced with something like
"Forms!MyForm!MyTextBox" so that I can specify how many records I want
in the textbox.

Any help would be appreciated.

[Wayne Aprato]

Thanks for the help Wayne.

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<qC***************@newssvr24.news.prodigy.com >...

It doesn't appear that it will accept a parameter for this value. Since you
are doing this on a form, you could rewrite the SQL of the query.

CurrentDb.QueryDefs("MyQuery").SQL = "SELECT TOP " & Me.txtMyTextbox &
"[SurName], [FirstName], [Company] FROM RandCont ORDER BY Rnd([Num]);"

--
Wayne Morgan
MS Access MVP
"Wayne Aprato" <cq*******@volcanomail.com> wrote in message
news:4d**************************@posting.google.c om...

I've played with this for several hours trying to make it work with no
success. How do I replace "TOP 4" in the following SQL statement with
the input from a textbox on a form?
If possible I want to do this in the query itself - not an SQL
statement in code.

SELECT TOP 4 [SurName], [FirstName], [Company]
FROM RandCont
ORDER BY Rnd([Num]);

I need "TOP 4" to be replaced with something like
"Forms!MyForm!MyTextBox" so that I can specify how many records I want
in the textbox.

Any help would be appreciated.