Solved

DB SQL Text String Question

Posted on 1998-10-27
15
133 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…

749 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