如何在VBA中使用多个条件与.Find？ [英] How to use multiple criteria with .Find in VBA?

问题描述

我想.FindLast来搜索特定的记录，它正在与一个标准，但是当我试图用.FindLast多个条件中停止工作。但是，我用几乎同样的语句.FindFirst和它的工作原理这就是为什么我很困惑。

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.

我得到的错误是在标准前pression数据类型不匹配。并且错误是在这行：rst.FindLast（DONOR_CONTACT_ID ='strDonor1'AND ORDER_NUMBER ='strOrderNum1'）。我踩在我的code和线路.FindFirst（DONOR_CONTACT_ID ='strDonor1和ORDER_NUMBER ='strOrderNum1'），但是正确地工作。

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.

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

编辑：

我只是增加了以下code：

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

这显示此与Debug.Print：

It displayed this with the Debug.Print:

DONOR_CONTACT_ID= '10136851341' AND ORDER_NUMBER= '112103071441001'

这些都是DONOR_CONTACT_ID和ORDER_NUMBER正确的价值观，但我得到的错误，在标准前pression数据类型不匹配的行rst.FindLast strFind。难道有可能是因为我定义我的变量变异？在表我已经DONOR_CONTACT_ID定义为十进制11 precision，RECIPIENT_CONTACT_ID定义为十进制11 precision，并ORDER_NUMBER为十进制15 precision。然后，我在我的code定义的变量变异。你觉得有可能是这个问题吗？

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?

推荐答案

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

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

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

要这样的事情...

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

在code抛出一个错误，或者干脆不找你期待什么，去立即窗口（控制 + <大骨节病>先按g ），并检查从 Debug.Print strFind 输出。你可能会立即发现了问题。如果不是，复制 Debug.Print 输出，打开查询设计一个新的查询，切换到SQL视图，并使用了复制的文本 WHERE 条款。在这种情况下，我想查询的SQL可能是：

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.Print output, open a new query in the query designer, switch to SQL View and use the copied text in a WHERE clause. In this case, I think the query SQL could be:

SELECT *
FROM T_RECIPIENT_SORT
WHERE yadda_yadda;

替换的 yadda_yadda 的的文字，你从即时窗口复制。

Replace yadda_yadda with the text you copied from the Immediate window.

这更像是一般的故障排除建议。对于这个特定的问题，我觉得你正在构建的查找文本包含的的名字的变量，而不是那些变量的值。看看你，当你 Debug.Print 这两个字符串EX pressions。

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

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

您code使用的第一个，但我认为你确实需要第二个。

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

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

In the update to your question you reported DONOR_CONTACT_ID and ORDER_NUMBER are both numeric data types. In that case do not quotes those search values in the Find string.

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

这篇关于如何在VBA中使用多个条件与.Find？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！