如何在 VBA 中对 .Find 使用多个条件？

How to use multiple criteria with .Find in VBA?

提问by nedstark179

I am trying to .FindLast to search for a specific record, and it was working with one criteria, but when I tried to use .FindLast with multiple criteria it stopped working. However, I use almost the same statement with .FindFirst and it works which is why I am confused.

我正在尝试使用 .FindLast 来搜索特定记录，并且它使用一个条件，但是当我尝试使用具有多个条件的 .FindLast 时，它停止工作。但是，我对 .FindFirst 使用几乎相同的语句并且它有效，这就是我感到困惑的原因。

The error I get is "Data type mismatch in criteria expression". And the error is for this line: rst.FindLast ("DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'"). I stepped through my code and the line .FindFirst ("DONOR_CONTACT_ID= 'strDonor1' and ORDER_NUMBER= 'strOrderNum1'") works correctly however.

我得到的错误是“条件表达式中的数据类型不匹配”。错误是针对这一行的：rst.FindLast（“DONOR_CONTACT_ID='strDonor1' AND ORDER_NUMBER='strOrderNum1'”）。我逐步完成了我的代码，但 .FindFirst ("DONOR_CONTACT_ID='strDonor1' and ORDER_NUMBER='strOrderNum1'") 行却正常工作。

Option Compare Database
Option Explicit

Public dbs As DAO.Database
Public rst As DAO.Recordset
Public rstOutput As DAO.Recordset
'Defines DAO objects
Public strDonor1 As Variant
Public strDonor2 As Variant
Public strRecip1 As Variant
Public strRecip2 As Variant
Public strOrderNum1 As Variant
Public strOrderNum2 As Variant
Public strLastDonor As Variant

Function UsingTemps()

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("T_RECIPIENT_SORT", dbOpenDynaset)
'rst refers to the table T_RECIPIENT_SORT
Set rstOutput = dbs.OpenRecordset("T_OUTPUT", dbOpenDynaset)
'rstOutput refers to the table T_OUTPUT

rst.MoveFirst
'first record
strDonor1 = rst!DONOR_CONTACT_ID
'sets strTemp1 to the first record of the DONOR_CONTACT_ID
strRecip1 = rst!RECIPIENT_CONTACT_ID
strOrderNum1 = rst!ORDER_NUMBER
rst.MoveNext
'moves to the next record

Do While Not rst.EOF
'Loop while it's not the end of the file
    strDonor2 = rst!DONOR_CONTACT_ID
    'strTemp2 = DONOR_CONTACT_ID from T_RECIPIENT_SORT
    strRecip2 = rst!RECIPIENT_CONTACT_ID
    strOrderNum2 = rst!ORDER_NUMBER
    'Sets strRecip = RECIPIENT_CONTACT_ID FROM T_RECIPIENT_SORT
    With rstOutput
    'Uses T_OUTPUT table
    If (strDonor1 = strDonor2) And (strOrderNum1 = strOrderNum2) Then
    'Runs if temps have same DONOR_CONTACT ID

            If .RecordCount > 0 Then
            'If table has records then you can check

                rst.FindLast ("DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'")
                strLastDonor = rst!RECIPIENT_CONTACT_ID
                If strLastDonor = strRecip2 Then
                    Call LastDonor
                Else
                    Call FirstDonor
                End If
            Else
            'No records in T_Output so needs to add first record
                .AddNew
                !DONOR_CONTACT_ID = strDonor1
                !RECIPIENT_1 = strRecip1
                !ORDER_NUMBER = strOrderNum1
                .Update
            End If
    Else
        .FindFirst ("DONOR_CONTACT_ID= 'strDonor1' and ORDER_NUMBER= 'strOrderNum1'")
        If .NoMatch Then
            .AddNew
            !DONOR_CONTACT_ID = strDonor1
            !RECIPIENT_1 = strRecip1
            !ORDER_NUMBER = strOrderNum1
            .Update
        End If

    End If
    End With
    'Slides variables down
    rst.FindFirst "[RECIPIENT_CONTACT_ID] = " & strRecip2
    strDonor1 = strDonor2
    strRecip1 = strRecip2
    strOrderNum1 = strOrderNum2
    rst.MoveNext

Loop

Call LastRecord

Set dbs = Nothing
Set rst = Nothing
Set rstOutput = Nothing

End Function

EDIT:

编辑：

I just added the following code:

我刚刚添加了以下代码：

Dim strFind As Variant
strFind = "DONOR_CONTACT_ID= '" & strDonor1 & "' AND ORDER_NUMBER= '" & strOrderNum1 & "'"
Debug.Print strFind
rst.FindLast strFind

It displayed this with the Debug.Print:

它用 Debug.Print 显示了这一点：

DONOR_CONTACT_ID= '10136851341' AND ORDER_NUMBER= '112103071441001'

These are the correct values for DONOR_CONTACT_ID and ORDER_NUMBER but I am getting the error "Data type mismatch in criteria expression" with the line rst.FindLast strFind. Could it possibly be because I defined my variables as variants? In the table I have DONOR_CONTACT_ID defined as Decimal with 11 precision, RECIPIENT_CONTACT_ID defined as Decimal with 11 precision, and ORDER_NUMBER as Decimal with 15 precision. I then define the variables in my code as variants. Do you think there could be a problem with this?

这些是 DONOR_CONTACT_ID 和 ORDER_NUMBER 的正确值，但我在 rst.FindLast strFind 行中收到错误“条件表达式中的数据类型不匹配”。可能是因为我将变量定义为变体？在表中，我将 DONOR_CONTACT_ID 定义为精度为 11 的十进制，将 RECIPIENT_CONTACT_ID 定义为精度为 11 的十进制，将 ORDER_NUMBER 定义为精度为 15 的十进制。然后我将代码中的变量定义为变体。你认为这可能有问题吗？

回答by HansUp

I think your trouble-shooting efforts will be easier if you change this ...

我认为如果你改变这个，你的故障排除工作会更容易......

rst.FindLast ("DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'")

to something like this ...

对这样的事情......

Dim strFind As String
strFind = "DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'"
Debug.Print strFind
rst.FindLast strFind

When the code throws an error, or simply doesn't find what you expect, go to the Immediate window (Ctrl+g) and inspect the output from Debug.Print strFind. You may spot the problem immediately. If not, copy the Debug.Printoutput, open a new query in the query designer, switch to SQL View and use the copied text in a WHEREclause. In this case, I think the query SQL could be:

当代码抛出错误，或者根本没有找到您期望的内容时，请转到立即窗口 ( Ctrl+ g) 并检查Debug.Print strFind. 您可能会立即发现问题。如果没有，请复制Debug.Print输出，在查询设计器中打开一个新查询，切换到 SQL 视图并在WHERE子句中使用复制的文本。在这种情况下，我认为查询 SQL 可能是：

SELECT *
FROM T_RECIPIENT_SORT
WHERE yadda_yadda;

Replace yadda_yaddawith the text you copied from the Immediate window.

将yadda_yadda替换为您从“立即”窗口复制的文本。

That was more like general trouble-shooting advice. For this specific problem, I think you're building the Findtext to include the namesof variables instead of those variables' values. See what you get when you Debug.Printthese 2 string expressions.

这更像是一般的故障排除建议。对于这个特定问题，我认为您正在构建Find文本以包含变量的名称而不是这些变量的值。看看当您使用Debug.Print这 2 个字符串表达式时会得到什么。

"DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'"
"DONOR_CONTACT_ID= '" & strDonor1 & "' AND ORDER_NUMBER= '" & strOrderNum1 & "'"

Your code used the first, but I think you actually need the second.

您的代码使用了第一个，但我认为您实际上需要第二个。

In the update to your question you reported DONOR_CONTACT_IDand ORDER_NUMBERare both numeric data types. In that case do not quotes those search values in the Findstring.

在您报告的问题的更新中DONOR_CONTACT_ID，ORDER_NUMBER都是数字数据类型。在这种情况下，不要在Find字符串中引用这些搜索值。

"DONOR_CONTACT_ID= " & strDonor1 & " AND ORDER_NUMBER= " & strOrderNum1

回答by Perry Sugerman

Could we have some missing data where the DONOR_CONTACT_ID matches but the ORDER_NUMBER is Null? I think Access would throw the kind of error you are getting from that situation.

我们是否有一些与 DONOR_CONTACT_ID 匹配但 ORDER_NUMBER 为 Null 的缺失数据？我认为 Access 会抛出您从这种情况中得到的那种错误。

Won't happen on FindFirst unless the first occurence is the culprit.

不会在 FindFirst 上发生，除非第一次发生是罪魁祸首。