[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

DB SQL Text String Question

Posted on 1998-10-27
15
Medium Priority
?
139 Views
Last Modified: 2010-04-30
I've got a input field for a last name. I built an SQL statement to pull this field and search a DB for the closest match. How do I account for the (') in a last name???

For example, when a user inputs, say, O'Malley (Gets assigned the variable, SelectUser), the program errors because of the ('). My SQL statement looks like this:

               strSQL = "Select * from PHONEDB WHERE [Last Name] Like '" & SelectUser & "*'"
0
Comment
Question by:sehenkel
[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
  • 8
  • 7
15 Comments
 
LVL 3

Accepted Solution

by:
vmano earned 200 total points
ID: 1441803
use double quotes around '
0
 
LVL 3

Expert Comment

by:vmano
ID: 1441804
here is more info:
Single quotation marks ('), double quotation marks ("), and the pipe symbol (|) are all special characters in SQL. Double up the quotation marks or, if you have only one type of quotation mark (single or double) in your data, use the other one as the delimiter.All other characters are handled as is.

let me know if this helps,
vmano
0
 

Author Comment

by:sehenkel
ID: 1441805
Edited text of question
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:sehenkel
ID: 1441806
Not sure if I follow what you are saying. Are you saying the the ("), (') and (|) are interchangeable? I tried the change but it did not work. Here's more code so you can see:


SelectUser = UCase(lblLastName.text) 'Assign variable from input field
strSQL = "Select * from PHONEDB WHERE [Last Name] LIKE ' " & SelectUser & "* ' " 

'Search Phone Database
Set DBPhones = OpenDatabase(pDBFileName)
Set RSPhones = DBPhones.OpenRecordset(strSQL, dbOpenSnapshot)
0
 
LVL 3

Expert Comment

by:vmano
ID: 1441807
OK. here is the code to convert your SQL:
   
   Dim nPos       As Integer
   Dim sTmp       As String
   Dim sSQL       As String

  strSQL = "Select * from PHONEDB WHERE [Last Name] LIKE ' " & SelectUser & "* ' " 
   sTmp = sSQL
   nPos = InStr(1, sTmp, "'")
   While nPos <> 0
      sTmp = Mid(sTmp, 1, nPos - 1) & "'" & "'" & Mid(sTmp, nPos + 1)
      nPos = InStr(nPos + 2, sTmp, "'")
   Wend
   strSQL = sTmp
   MsgBox strSQL

after this conversion your SQL should work fine.

let me know if  this helps,
vmano
0
 
LVL 3

Expert Comment

by:vmano
ID: 1441808
a correction to the above comment.just replace sSQL with strSQL.
sorry for the confusion.

vmano
0
 

Author Comment

by:sehenkel
ID: 1441809
Ok, after the coversion, my SQL statement looks like this (inputting O'Malley as a last name):

    Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

When trying to open the recordset, I get a 3075 error
0
 

Author Comment

by:sehenkel
ID: 1441810
Ok, after the coversion, my SQL statement looks like this (inputting O'Malley as a last name):

    Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

When trying to open the recordset, I get a 3075 error
0
 
LVL 3

Expert Comment

by:vmano
ID: 1441811
i think your SQL statement should be:
strSQL = "Select * from PHONEDB WHERE [Last Name] LIKE " & SelectUser & "*"
0
 

Author Comment

by:sehenkel
ID: 1441812
The SQL line I typed was what the value of the srtSQL variable was when debugging. In the code, my line reads like this:

strSQL = "Select * from PHONEDB_CT WHERE [Last Name] LIKE '" & SelectUser & "*'"

When debugging (inputting in O'Malley into the program) it looks like this:

Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

Sorry, I should have made that clearer.
0
 

Author Comment

by:sehenkel
ID: 1441813
The SQL line I typed was what the value of the srtSQL variable was when debugging. In the code, my line reads like this:

strSQL = "Select * from PHONEDB_CT WHERE [Last Name] LIKE '" & SelectUser & "*'"

When debugging (inputting in O'Malley into the program) it looks like this:

Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

Sorry, I should have made that clearer.
0
 
LVL 3

Expert Comment

by:vmano
ID: 1441814
some problem with your SQL.try this (just cut and paste):
strSQL = "Select * from PHONEDB_CT WHERE [Last Name] LIKE '" & SelectUser & "'*"

this should do it.

vmano
0
 

Author Comment

by:sehenkel
ID: 1441815
The SQL line I typed was what the value of the srtSQL variable was when debugging. In the code, my line reads like this:

strSQL = "Select * from PHONEDB_CT WHERE [Last Name] LIKE '" & SelectUser & "*'"

When debugging (inputting in O'Malley into the program) it looks like this:

Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

Sorry, I should have made that clearer.
0
 
LVL 3

Expert Comment

by:vmano
ID: 1441816
have you tried my SQL (comment) posted at Tuesday, October 27 1998 - 10:40AM PST. there is a difference between this SQL and what you are trying. please try to notice the difference carefully, and please try what i gave you and let me know.

vmano
0
 

Author Comment

by:sehenkel
ID: 1441817
That did the trick. Thanks so much for your help!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

656 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