Solved

Syntax error

Posted on 2013-01-10
15
295 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

825 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