Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Syntax error

Microsoft Office Access 2003

Syntax error (missing operator) in query expression ‘emailid=’Amanda O’Guinn/IL/XYZ’ or GivenName = ‘Amanda O’Guinn/IL/XYZ”


I know that the error is due to the extra ' in Amanda's last name . Any help to avoid this error is appreciated...


thanks.
0
eiyengars
Asked:
eiyengars
  • 5
  • 5
  • 3
2 Solutions
 
mbizupCommented:
Use double quotes around the text:

emailid= "Amanda O’Guinn/IL/XYZ" or
0
 
mbizupCommented:
If you are building a SQL String in vba:

strSQL = "SELECT * FROM YourTable WHERE emailid= " & Chr(34) & "Amanda O’Guinn/IL/XYZ" & chr(34) & " or GivenName = " & chr(34) & "Amanda O’Guinn/IL/XYZ" & Chr(34)

Open in new window


Using a variable:
strName = "Amanda O’Guinn/IL/XYZ"
strSQL = "SELECT * FROM YourTable WHERE emailid= " & Chr(34) & strName & chr(34) & " or GivenName = " & Chr(34) & strName & chr(34) 

Open in new window

0
 
Dale FyeCommented:
How are you building this criteria string, or are you trying to assign this as a value?

I have a function I use (fnWrap), which allows me to wrap text strings in some other string (one or more characters).  It also allows you to replace instances of a single quote inside a string with two single quotes '', which is what it takes for both Jet and SQL Server to properly interpret an imbedded quote or single quote.  The function looks like:
Public Function fnWrap(WrapWhat as Variant, Optional WrapWith as String = """") as string

    fnWrap = WrapWith & Replace(WrapWhat, WrapWith, WrapWith & WrapWith) & WrapWith

End Function

Open in new window

When creating a criteria string from a string in a textbox, or a field in a table, I would use syntax similar to:

strCriteria = "[emailID] = " & fnWrap(me.txtName, "'")

I find this technique significantly easier to read and use than trying to pre/post pend extra single or double quotes, and it can even be use to append #'s around a date value that is entered into a textbox.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
eiyengarsAuthor Commented:
I am reading the name from a table and using the name to send emails.. I have to use the apostrophe in the name to send the email.. will this method help me to avoid the error?


Thanks.
0
 
mbizupCommented:
Can you post the lines of code that are causing the error?
0
 
eiyengarsAuthor Commented:
Public Function PSAData()
Dim strDebug As String
    Dim objRS As Recordset
    Dim strEmailQ2 As String
    Dim strEmailQ3 As String
    Dim strSurveySubmited As String
    Dim strAttachment As String
    Dim bCreated As Boolean
    Dim bImported As Boolean
    Dim bSend As Boolean
    Dim strError As String
   
    Dim TempPath As String
   
   
    bCreated = IsDateCreated
   
    If bCreated = True Then
        bSend = IsEmailPSASend
        If bSend = True Then
            MsgBox "PSA Data: All email notices for today have been sent"
            Exit Function
        End If
    End If
   
    strDebug = "select * from qryGreaterthanOrEqualOne_C"
    Set objRS = Application.CurrentDb.OpenRecordset(strDebug)
   
    While Not objRS.EOF
   
    strEmailQ2 = objRS.Fields(0)
    strEmailQ3 = objRS.Fields(1)
    strSurveySubmited = objRS.Fields(2)
       
    strAttachment = PDF_Save(strSurveySubmited, "rpt_CDNoASVP")
   
    strError = ValidateEmails(strEmailQ2, strEmailQ3)
   
    If Len(strError) > 0 Then
        LogDetails strEmailQ2, strEmailQ3, strSurveySubmited, strAttachment, "PSAData", strError
    Else
        LotusMail strEmailQ2, strEmailQ3, strAttachment
    End If
       
    'MsgBox strAttachment & " File has send to " & strEmailQ2 & "     " & strEmailQ3
   
    objRS.MoveNext
   
    TempPath = Application.CurrentProject.path + "\tempPDFInvoice.snp"
   
   
    Kill (TempPath)
    'Kill (strAttachment)
   
    Wend
   
    CreateDateWithPSASendFlag Not bCreated, True
     
    If Not bValueSilentMode Then
    MsgBox "PSA Data:Email sent to all recipients"
    End If
   
End Function
0
 
eiyengarsAuthor Commented:
Here's where the error is occuring..

Public Function ValidateEmails(stremail1 As String, stremail2 As String) As String

    Dim strDebug As String
    Dim objRS As Recordset
    Dim strEmailQ2 As String
    Dim strEmailQ3 As String
    Dim strSurveySubmited As String
    Dim strAttachment As String
    Dim strErrormessage As String
   
    If bValidateEmail = True Then
        strDebug = "select ID from tblContacts where emailid = '" & stremail1 & "' or GivenName = '" & stremail1 & "'"
        Set objRS = Application.CurrentDb.OpenRecordset(strDebug)
       
        If Not objRS.EOF Then
       
        Else
            strErrormessage = " The Email Q2 : " & stremail1 & " not present in the local contacts "
        End If
       
        objRS.Close
       
        strDebug = "select ID from tblContacts where emailid = '" & stremail2 & "' or GivenName = '" & stremail2 & "'"
        Set objRS = Application.CurrentDb.OpenRecordset(strDebug)
       
        If Not objRS.EOF Then
       
        Else
            strErrormessage = strErrormessage & " The Email Q3 : " & stremail2 & " not present in the local contacts "
        End If
       
        objRS.Close
    End If
   
    ValidateEmails = strErrormessage
End Function
0
 
mbizupCommented:
Try this:

Public Function PSAData()
Dim strDebug As String
    Dim objRS As Recordset
    Dim strEmailQ2 As String 
    Dim strEmailQ3 As String
    Dim strSurveySubmited As String
    Dim strAttachment As String
    Dim bCreated As Boolean
    Dim bImported As Boolean
    Dim bSend As Boolean
    Dim strError As String
    
    Dim TempPath As String
    
    
    bCreated = IsDateCreated
    
    If bCreated = True Then
        bSend = IsEmailPSASend
        If bSend = True Then
            MsgBox "PSA Data: All email notices for today have been sent"
            Exit Function
        End If
    End If
    
    strDebug = "select * from qryGreaterthanOrEqualOne_C"
    Set objRS = Application.CurrentDb.OpenRecordset(strDebug)
    
    While Not objRS.EOF
    
    strEmailQ2 = Replace(objRS.Fields(0),"'","''")
    strEmailQ3 = Replace(objRS.Fields(1),"'","''")
    strSurveySubmited =replace( objRS.Fields(2),"'","''")
        
    strAttachment = PDF_Save(strSurveySubmited, "rpt_CDNoASVP")
    
    strError = ValidateEmails(strEmailQ2, strEmailQ3)
    
    If Len(strError) > 0 Then
        LogDetails strEmailQ2, strEmailQ3, strSurveySubmited, strAttachment, "PSAData", strError
    Else
        LotusMail strEmailQ2, strEmailQ3, strAttachment
    End If
        
    'MsgBox strAttachment & " File has send to " & strEmailQ2 & "     " & strEmailQ3
    
    objRS.MoveNext
    
    TempPath = Application.CurrentProject.path + "\tempPDFInvoice.snp"
    
    
    Kill (TempPath)
    'Kill (strAttachment)
    
    Wend
    
    CreateDateWithPSASendFlag Not bCreated, True
      
    If Not bValueSilentMode Then
    MsgBox "PSA Data:Email sent to all recipients"
    End If
    
End Function

Open in new window

0
 
Dale FyeCommented:
I don't see where you are using that syntax anywhere in this code.  All I see is:

    strEmailQ2 = objRS.Fields(0)
    strEmailQ3 = objRS.Fields(1)
   
I'm guessing that any problem you are encountering is in one of these functions:

 ValidateEmails(strEmailQ2, strEmailQ3)

or

       LotusMail strEmailQ2, strEmailQ3, strAttachment

What does the VBA for those functions look like?
0
 
Dale FyeCommented:
Yes, fnWrap would work here.  Replace:

        strDebug = "select ID from tblContacts where emailid = '" & stremail1 & "' or GivenName = '" & stremail1 & "'"

With

        strDebug = "select ID from tblContacts" _
                         & " Where emailid = " & fnWrap(stremail1, "'") _
                         & " OR GivenName = " & fnWrap(stremail1. "'")

Then doe the same with the strDebup = ...stremail2 line as well
0
 
eiyengarsAuthor Commented:
Thank you everyone... it worked...
0
 
eiyengarsAuthor Commented:
I want to make sure you both get the points... Please let me know if you have any questions...
0
 
mbizupCommented:
That didn't quite work out. :-)

If you click the Request Attention button in the original post a Mod can reopen it for you.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now