MSAccessVBA與通配符不同

我有一個 VBA 宏，可以向我們的供應商發送未結訂單的電子郵件。我正在嘗試更改代碼以洗掉我們正在等待發票的未結訂單。它正在將一些電子郵件復制給錯誤的供應商。以下是不起作用的代碼：

Option Compare Database

Sub sSendFollowUpEMailOrder()
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsVendor As DAO.Recordset
    Dim rsOrder As DAO.Recordset
    Dim objOL As New Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim strSQL As String
    Dim emailTo As String
    Dim emailText As String
    
    Set db = CurrentDb
    strSQL = "SELECT DISTINCT V.[Vendor Number], V.EMail" _
                & " FROM qry002OpenOrders AS I LEFT JOIN tblVendors AS V ON I.[Vendor Nbr] = V.[Vendor Number] " _
                & " WHERE ((Not (V.[Vendor Number]) Is Null) AND ((I.[Document Date])<=Date()-30)) " _
                & " ORDER BY V.[Vendor Number];"
    Set rsVendor = db.OpenRecordset(strSQL)
    If Not (rsVendor.BOF And rsVendor.EOF) Then
        Do
            strSQL = "SELECT DISTINCT I.[Vendor Nbr], I.[Vendor Name], I.[Delivery Date], I.[Document Date], I.[Purchasing Document], I.Item, I.[Short Text], I.[Order Quantity] " _
                        & " FROM qry002OpenOrders AS I " _
                        & " WHERE (((I.[Vendor Nbr])=" & rsVendor("Vendor Number") & ")" _
                        & "  AND ((I.[Delivery Date])<=Date()-30) " _
                        & "  AND ((I.[Short Text]) Not Like 'INV*')) " _
                        & " ORDER BY I.[Vendor Name], I.[Purchasing Document], I.Item;"
            Set rsOrder = db.OpenRecordset(strSQL)
            If Not (rsOrder.BOF And rsOrder.EOF) Then
                emailSubject = "Open Orders"
                emailText = "Please provide estimated ship date and pricing for the below Purchase Orders:"
                emailText = emailText & vbCrLf & "Pur. Doc." & vbTab & "     " & "LI" & vbTab & "    " & "Qty" & vbTab & "    " & "Description"
                Do
                    emailText = emailText & vbCrLf & rsOrder("Purchasing Document") & vbTab & rsOrder("Item") & vbTab & rsOrder("Order Quantity") & vbTab & rsOrder("Short Text")
                    rsOrder.MoveNext
                Loop Until rsOrder.EOF
            End If
            emailTo = rsVendor!EMail
            emailTo = emailTo & ";[email protected]"
            'emailTo = emailTo & ";[email protected]"
            Set objMail = objOL.CreateItem(olMailItem)
            objMail.To = emailTo
            objMail.Subject = emailSubject
            objMail.Body = emailText
            objMail.Send
            rsVendor.MoveNext
        Loop Until rsVendor.EOF
    End If
sExit:
    On Error Resume Next
    rsVendor.Close
    rsOrder.Close
    Set rsVendor = Nothing
    Set rsOrder = Nothing
    Set db = Nothing
    Set objMail = Nothing
    'objOL.Quit
    Set objOL = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbcrfl & "sSendFollowUpEMail", vbOKOnly   vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

uj5u.com熱心網友回復：

您必須將 "INV*" 放入單引號 --> 'INV*' （沒有雙雙引號）

strSQL = "SELECT DISTINCT I.[Vendor Nbr], I.[Vendor Name], I.[Delivery Date], I.[Document Date], I.[Purchasing Document], I.Item, I.[Short Text], I.[Order Quantity] " _
                   & " FROM qry002OpenOrders AS I " _
                    & " WHERE (((I.[Vendor Nbr])=" & rsVendor("Vendor Number") & ")" _
                    & "  AND ((I.[Delivery Date])<=Date()-30)) " _
                    & "  AND ((I.[Short Text]) Not Like 'INV*')) " _
                    & " ORDER BY I.[Purchasing Document];"

標籤：

上一篇：獲取資料庫檔案名

下一篇：為什么這不驗證選擇？