apostrophe select sql using Like 1

[woro2006]

I can't seem to make this work, as I don't have a query string to apply replace(str, "'", "''"). I am trying to query the database for all records that contain an apostrophe. For example, O'connor, O'Donald, would be returned. After that, the replace function would take out the apostrophes (i.e. Oconnor, and ODonald).

Option Compare Database

Sub testnow()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim asql As String
Set cnn = CurrentProject.Connection
'asql = "select * from atable"
asql = "Select * from atable where test Like " & """" & "*'*" & """"
Debug.Print (asql)

Set rst = New ADODB.Recordset

With rst
.Open asql, cnn, adOpenDynamic, adLockOptimistic

Do Until .EOF

Debug.Print (rst("test"))
'.Fields("test") = Replace(.Fields("test"), "'", "")
.MoveNext

Loop

End With


MsgBox "connection made"
rst.Close
Set rst = Nothing

End Sub

 

[jebry]

Hi!

Why to you need to find all of the names with apostrophes? I think the replace function will just return the original string if there are no occurrances of the find string.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[woro2006]

Because I want to do dlookup with the apostrophes stripped out.

 

[traingamer]

What about just

asql = "Select * from atable where test Like " & "*'*"

?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[jebry]

Hi again!

If you make this query:

Select Field1, Field2, etc, Replace(test, "","'") As FieldTest from atable

This will return all of table with the apostrophes stripped out of the test field. You can then do a dLookUp on that query. Or am I missing something?

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[traingamer]

Or am I missing something?

Probably not.

 

[PHV]

As you use ADO the wildchar is %, not *:

asql = "SELECT * FROM atable WHERE test LIKE '%''%'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[woro2006]

PHV solved my problem. It worked like a charm.

 

[traingamer]

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill