Solved

DB SQL Text String Question

Posted on 1998-10-27
15
134 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 50 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…

724 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