Solved

Syntax error

Posted on 2013-01-10
15
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Expert Comment

by:Dale Fye
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
CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

 

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 48

Expert Comment

by:Dale Fye
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 48

Assisted Solution

by:Dale Fye
Dale Fye 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

630 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