Solved

Syntax error

Posted on 2013-01-10
15
298 Views
Last Modified: 2013-02-02
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
Comment
Question by:eiyengars
  • 5
  • 5
  • 3
15 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38765552
Use double quotes around the text:

emailid= "Amanda O’Guinn/IL/XYZ" or
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 38765557
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38765565
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:eiyengars
ID: 38765579
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38765582
Can you post the lines of code that are causing the error?
0
 

Author Comment

by:eiyengars
ID: 38765594
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
 

Author Comment

by:eiyengars
ID: 38765603
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38765604
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38765605
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 38765613
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
 

Author Comment

by:eiyengars
ID: 38765679
Thank you everyone... it worked...
0
 

Author Comment

by:eiyengars
ID: 38765681
I want to make sure you both get the points... Please let me know if you have any questions...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38765687
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question