vba 在不循环的情况下获取记录集字段中的最小值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9821970/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
getting minimum value in a recordset field without Looping
提问by Heman
I am using Access 2007 and writing a macro in VBA. "Analyze" subroutine is passed a recordset rev_rec. This recordset has two fields "PeriodNo" (Integer) and "Revenue"(double). Minrev is a variable where I have to store the minimum value of "Revenue" field. The code that I am using is below.
我正在使用 Access 2007 并在 VBA 中编写一个宏。“分析”子例程被传递一个记录集rev_rec。这个记录集有两个字段“PeriodNo”（整数）和“收入”（双）。Minrev 是一个变量，我必须在其中存储“收入”字段的最小值。我正在使用的代码如下。
Public Sub Analyze(rev_rec As DAO.Recordset)
Dim Minrev As Double
rev_rec.MoveFirst
Minrev = rev_rec("Revenue")
While Not rev_rec.EOF
If rev_rec("Revenue") < Minrev Then
Minrev = rev_rec("Revenue")
End If
rev_rec.MoveNext
Wend
rev_rec.MoveFirst
.
.
.
End Sub
I tried using DMin() with recordset but am not able to find a way to do that. Is there a way to do this without using any kind of loop?
我尝试将 DMin() 与记录集一起使用，但找不到方法来做到这一点。有没有办法在不使用任何循环的情况下做到这一点？
回答by Andy Brown
The problem is that you're passing a recordset. If you just had the table or query name (or SQL statement), you could just use DMIN. Eg:
问题是你正在传递一个记录集。如果您只有表或查询名称（或 SQL 语句），则可以使用 DMIN。例如：
MinRev = DMIN("Revenue","TableOrQueryNameInQuotes","")
The third argument can be used to set some criteria. For example:
第三个参数可用于设置一些标准。例如：
MinRev = DMIN("Revenue","TableOrQueryNameInQuotes","PeriodNo > 5")
Be warned, however, that the functions starting with D (DMIN, DLOOKUP, DSUM) run very slowly, although if you've got less than about 10,000 records you won't notice this.
但是请注意，以 D 开头的函数（DMIN、DLOOKUP、DSUM）运行非常缓慢，但如果您的记录少于 10,000 条，您将不会注意到这一点。
回答by HK1
I think your best bet is to build a new recordset using a SQL statement that only retrieves one record, the one with the minimum for the desired period. Another option is, you could open this particular recordset with the Order By on the Revenue column Ascending. This way you would know that the smallest value will be in the first record.
我认为最好的办法是使用 SQL 语句构建一个新的记录集，该语句只检索一条记录，即在所需时间段内具有最小值的记录。另一种选择是，您可以使用“收入”列升序上的“订购者”打开此特定记录集。这样你就会知道最小值将在第一条记录中。
Andy Brown's suggestion of using DMin should also work. It's actually an idea very similar to my first suggestion.
Andy Brown 使用 DMin 的建议也应该有效。这实际上与我的第一个建议非常相似。
