?
Solved

DB SQL Text String Question

Posted on 1998-10-27
15
Medium Priority
?
143 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
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

594 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