Solved

SQL = ". .like '" & Mike's Garage & "*'"

Posted on 1998-11-19
7
250 Views
Last Modified: 2006-11-17
Variable = "Mike's Garage"
SQL = "..like '" & Variable & "*'". . the variable used contains a possesive "Mike's" which fouls up the SQL statement because of the apostrophe.  How can I parse this out so the SQL looks for the record, since the record says "Mike's Garage" not "Mikes Garage".  The SQL statement, because of the apostrophe in the name, stops at 'Mike' looking for an operator.  help help.
0
Comment
Question by:RUSTY
  • 4
  • 3
7 Comments
 

Author Comment

by:RUSTY
ID: 1445660
Edited text of question
0
 

Author Comment

by:RUSTY
ID: 1445661
Edited text of question
0
 

Author Comment

by:RUSTY
ID: 1445662
Edited text of question
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 18

Accepted Solution

by:
deighton earned 30 total points
ID: 1445663
instead of the ' use ''
0
 
LVL 18

Expert Comment

by:deighton
ID: 1445664
'Heres a function to put in the extra ' where it is needed

Private Function boggle(s As String) As String

    Dim stemp As String
    Dim c As Integer
   

   For c = 1 To Len(Text1)
   
 If Mid(Text1, c, 1) = "'" Then        'NB the "'" is <double quote><single Qt><dbl quote>
       
            stemp = stemp & "'"      'quote sequence as above
           
        End If
       
        stemp = stemp & Mid(Text1, c, 1)
       
    Next
   
    boggle = stemp

End Function

0
 
LVL 18

Expert Comment

by:deighton
ID: 1445665
I need to be clearer because of the way it looks on screen where you have say

O'Brien you must change the ' to two adjacent single quotes i.e ' followed by ' (rather than double quotes)  my function boggle does this for you, once youve boggled your string use it in your sql.
0
 

Author Comment

by:RUSTY
ID: 1445666
Brilliant!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

778 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