CODEWITHSUNDEEP
sqlvbams-access
Terry Milnes
Terry Milnes

Reputation: 7

MS Access VBA Not Like with wild card

I have a VBA macro that sends emails to our vendors with their open orders. I am trying to change the code to remove open orders that we are just waiting for an invoice. It is duplicating some emails to the wrong vendors. Below is the code that is not working:

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

Upvotes: 0

Views: 268

Answers (1)

Ike
Ike

Reputation: 13064

You have to put "INV*" into single quotes --> 'INV*' (no double double quotes)

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];"

Upvotes: 1

Related Questions