By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

Wild Card in ADO

P: n/a
Have been trying to convert recordsets from DAO to ADO.

Program worked fine in DAO but am having trouble using wildcard in SQL string
for ADO recordset.

The problem is in the "Where" clause. Here are the two versions:
XXXXXXXXXXXXXXXXXXXXXXXXX

DAO version:

Dim Db as Database
Dim LN As String
Dim strSQL As String
Dim rst As Recordset

Set db = CurrentDb

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "*"" )); "

Set rst = db.OpenRecordset(strSQL)

This works great in DAO.

ADO version:

Dim LN As String
Dim strSQL As String
Dim rst As New ADODB.Recordset

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME] " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "%"" )); "

rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

This does not work in ADO

XXXXXXXXXXXXXXXXXXXXXXXXXXX

I have tried many versions of the syntax in the "Where" clause but can't seem
to get the wildcard to work properly in the ADO version. Note the change fro
"*" to
%" for the wildcard but that does not work either.

Any suggestions?

Thank you for the help.

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
mf***@aol.com (MFS 43) wrote in
news:20***************************@mb-m28.aol.com:
Have been trying to convert recordsets from DAO to ADO.

Program worked fine in DAO but am having trouble using wildcard in SQL
string for ADO recordset.

The problem is in the "Where" clause. Here are the two versions:
XXXXXXXXXXXXXXXXXXXXXXXXX

DAO version:

Dim Db as Database
Dim LN As String
Dim strSQL As String
Dim rst As Recordset

Set db = CurrentDb

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "*"" )); "

Set rst = db.OpenRecordset(strSQL)

This works great in DAO.

ADO version:

Dim LN As String
Dim strSQL As String
Dim rst As New ADODB.Recordset

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME] " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "%"" )); "

rst.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

This does not work in ADO

XXXXXXXXXXXXXXXXXXXXXXXXXXX

I have tried many versions of the syntax in the "Where" clause but can't
seem to get the wildcard to work properly in the ADO version. Note the
change fro "*" to
%" for the wildcard but that does not work either.

Any suggestions?

Thank you for the help.


Are you connecting to SQL Server? If so try using single quotes for your
string delimiters.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #2

P: n/a
Try

....
& "WHERE ((([PatientDemographics].[LASTNAME]) Like '" & LN & "%"' )); "

i.e. single quotes instead of the embedded double quotes.
--
Terry Kreft
MVP Microsoft Access
"MFS 43" <mf***@aol.com> wrote in message
news:20***************************@mb-m28.aol.com...
Have been trying to convert recordsets from DAO to ADO.

Program worked fine in DAO but am having trouble using wildcard in SQL string for ADO recordset.

The problem is in the "Where" clause. Here are the two versions:
XXXXXXXXXXXXXXXXXXXXXXXXX

DAO version:

Dim Db as Database
Dim LN As String
Dim strSQL As String
Dim rst As Recordset

Set db = CurrentDb

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "*"" )); "

Set rst = db.OpenRecordset(strSQL)

This works great in DAO.

ADO version:

Dim LN As String
Dim strSQL As String
Dim rst As New ADODB.Recordset

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME] " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "%"" )); "

rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
This does not work in ADO

XXXXXXXXXXXXXXXXXXXXXXXXXXX

I have tried many versions of the syntax in the "Where" clause but can't seem to get the wildcard to work properly in the ADO version. Note the change fro "*" to
%" for the wildcard but that does not work either.

Any suggestions?

Thank you for the help.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.