Solved

Syntax error

Posted on 2013-01-10
15
289 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now