Solved

SQL / ADO Problem

Posted on 2000-03-20
6
145 Views
Last Modified: 2013-11-23
Hi

I am using vb5 /ADO2.1 with SQL Server 7 and opening a recordset with the following statement:

rs.open "SELECT surname FROM patient WHERE surname LIKE 'O''%'
ORDER BY surname",aDB, adOpenForwardOnly, adLockReadOnly

When I list the results the names are in the following order:

O' Brien
O Donnell
O' Donnell
O' Grady
Oakes
Oakey
O'Brien

How do I only list the names with a ' in them. It appears to be ignoring the ' when sorting.

Thanks
0
Comment
Question by:sbmc
6 Comments
 
LVL 2

Accepted Solution

by:
p_biggelaar earned 100 total points
ID: 2639538
Instead of WHERE surname LIKE 'O''%' which also returns the other names, you could ask more specifically for WHERE LEFT(surname, 2)='O'''. Hope this is what you need.
0
 
LVL 1

Expert Comment

by:Kurvy
ID: 2639634
Amend your sql statement so that the like wild card search treats the ' as a character instead of a special character
ie
rs.open "SELECT surname FROM patient WHERE surname LIKE 'O''%' amend it to
rs.open "SELECT surname FROM patient WHERE surname LIKE "O%'%"

This should give you only the names taht start O'whatever

0
 
LVL 2

Expert Comment

by:damienm
ID: 2639680
Use:

rs.open "SELECT surname FROM patient WHERE surname LIKE " & """" & "O'% " & """" & "ORDER BY surname",aDB, adOpenForwardOnly, adLockReadOnly
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Expert Comment

by:damienm
ID: 2639685
That should be:

rs.open "SELECT surname FROM patient WHERE surname LIKE " & """" & "O'%" & """" & " ORDER BY surname",aDB, adOpenForwardOnly, adLockReadOnly
0
 
LVL 2

Author Comment

by:sbmc
ID: 2642876
damienm

Couldnt get this to work at all. Thanks anyway.


p_biggelaar

Thanks. Your solution worked. If you care to resubmit as answer you get the points.

sbmc
0
 
LVL 2

Expert Comment

by:p_biggelaar
ID: 2643781
Guess I don't need to resubmit?!

Glad to be of help...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now