限制基于表单控件返回的记录数 [英] 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]]

..但这没用.

推荐答案

是否有一种方法可以基于文本框中的用户输入来限制查询中返回的记录数?"

Access SQL不能使用SELECT TOP接受任何类型的参数.

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

不幸的是，当您尝试尝试时，错误消息(#3141)不能清楚地识别问题:

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

"SELECT语句包含一个保留字或一个拼写错误或丢失的参数名称，或者标点符号不正确."

如果可以使用VBA构建SELECT，则可以按照Andy所述将数字构建到字符串中，从而轻松解决该问题.或者，您可以使用Replace()替换您的MailoutSize值，以代替预构建SQL语句中的占位符.

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.

所以从...开始

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.

这篇关于限制基于表单控件返回的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！