CODEWITHSUNDEEP
sqlms-accessms-access-2007
TinaB
TinaB

Reputation: 15

Limit # of records returned based on a form control

Is there a way to limit the number of records returned in a query based on the user input in a text box? I know that I could use the Top Values if the number of records were constant, but this number will change. I tried: 

SELECT TOP[Forms![frm_GenerateMailout]![MailoutSize]]

..but that didn't work.

Upvotes: 1

Views: 388

Answers (2)

HansUp
HansUp

Reputation: 97101

"Is there a way to limit the number of records returned in a query based on the user input in a text box?"

Access SQL does not accept a parameter of any kind with SELECT TOP.

Unfortunately, when you try, the error message (#3141) does not identify the problem clearly:

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

If you can build your SELECT with VBA, you can work around that easily enough by building the number into the string as Andy described. Or you can use Replace() to substitute your MailoutSize value for a place-holder in a pre-built SQL statement.

So starting with ...

strTemplateSql = "SELECT TOP 100 PERCENT tblFoo.*" & vbCrLf & _
   "FROM tblFoo;"

You can do this ...

strSelect = Replace(strTemplateSql, "100 PERCENT", _
    Forms!frm_GenerateMailout!MailoutSize)

There are other possibilities, too. If neither of our suggestions is appropriate in your situation, tell us about where and how you need to use the query.

Upvotes: 1

Andy G
Andy G

Reputation: 19367

It depends how, and where, you are using the SQL statement, but if you construct it as a string you need to concatenate it correctly:

"SELECT TOP " & Forms!frm_GenerateMailout!MailoutSize & ".. etc."

Upvotes: 1

Related Questions