|
 |
access thread: Problem with "LIKE" query in VBA
Message #1 by "Gerald, Rand" <RGerald@u...> on Fri, 19 Jul 2002 15:10:07 -0500
|
|
I am trying to create a "LIKE" query string in VBA.
If I create the query in the Query window and run it, two records are
returned.
If I then go to the SQL window and copy the SQL text verbatim (See
Example
1) it fails with a syntax error. Obviously caused by misinterpretation
of
the doublequote characters.
Variations on the string run but do not return any records (See
Examples 2
and 3).
The ultimate goal is to be able to build the SQL string using a Text
Box
similar to Example 4.
Example 1
"SELECT DISTINCT [tblPeople].[RegistrationID] FROM tblPeople WHERE
((([tblPeople].[LastName]) Like "smi*"));"
'1 - Does Not Work At All - Syntax Error: Copy From Working SQL Window
Example 2
"SELECT DISTINCT [tblPeople].[RegistrationID] FROM tblPeople WHERE
((([tblPeople].[LastName]) Like ""smi*""));"
'2 - Does Not Work As Desired - Returns zero records.
Example 3
"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
((tblPeople.LastName) LIKE " & Chr(34) & "smi" & "*" & Chr(34) & ");"
'3 - Does Not Work As Desired - Returns zero records.
Example 4 - Actual goal
"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
(((tblPeople.LastName) LIKE '" & CStr(txtSearchValue.Value) & "*'));"
'4 - Does Not Work As Desired - Returns zero records.
Has anyone seen this before and do you know of a work around?
Thanks,
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
Message #2 by "Carnley, Dave" <dcarnley@a...> on Fri, 19 Jul 2002 15:29:21 -0500
|
|
If the SQL is to be interpreted by Access, then as far as I can tell
single
or double quotes doesn't matter so you can use singles in constructing
your
string, that is easier.
If the query is going out to SQL Server, then you should use single
quotes,
and the wildcard character is % not *.
I don't see any reason this shouldn't work the way it is written in
Example
4, so I would say, look to your run-time conditions. Specifically, the
value of CStr(txtSearchValue.Value). Make sure it is exactly what you
expect. Try eliminating some of those parentheses they should
theoretically
be irrelevant but you never know...
"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
tblPeople.LastName LIKE '" & CStr(txtSearchValue.Value) & "*';"
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Friday, July 19, 2002 3:10 PM
To: Access
Subject: [access] Problem with "LIKE" query in VBA
Importance: High
I am trying to create a "LIKE" query string in VBA.
If I create the query in the Query window and run it, two records are
returned.
If I then go to the SQL window and copy the SQL text verbatim (See
Example
1) it fails with a syntax error. Obviously caused by misinterpretation
of
the doublequote characters.
Variations on the string run but do not return any records (See
Examples 2
and 3).
The ultimate goal is to be able to build the SQL string using a Text
Box
similar to Example 4.
Example 1
"SELECT DISTINCT [tblPeople].[RegistrationID] FROM tblPeople WHERE
((([tblPeople].[LastName]) Like "smi*"));"
'1 - Does Not Work At All - Syntax Error: Copy From Working SQL Window
Example 2
"SELECT DISTINCT [tblPeople].[RegistrationID] FROM tblPeople WHERE
((([tblPeople].[LastName]) Like ""smi*""));"
'2 - Does Not Work As Desired - Returns zero records.
Example 3
"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
((tblPeople.LastName) LIKE " & Chr(34) & "smi" & "*" & Chr(34) & ");"
'3 - Does Not Work As Desired - Returns zero records.
Example 4 - Actual goal
"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
(((tblPeople.LastName) LIKE '" & CStr(txtSearchValue.Value) & "*'));"
'4 - Does Not Work As Desired - Returns zero records.
Has anyone seen this before and do you know of a work around?
Thanks,
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
Message #3 by "Leo Scott" <leoscott@c...> on Fri, 19 Jul 2002 13:33:45 -0700
|
|
If you are using like with a string value enclose the like value in '.
Example:
SELECT LastName, FirstName FROM People WHERE LastName LIKE 'A*'
That is if you are using DAO. If you are using ADODB then substitute % for
*.
|-----Original Message-----
|From: Gerald, Rand [mailto:RGerald@u...]
|Sent: Friday, July 19, 2002 1:10 PM
|To: Access
|Subject: [access] Problem with "LIKE" query in VBA
|Importance: High
|
|
|I am trying to create a "LIKE" query string in VBA.
|
|If I create the query in the Query window and run it, two records are
|returned.
|If I then go to the SQL window and copy the SQL text verbatim (See Example
|1) it fails with a syntax error. Obviously caused by misinterpretation of
|the doublequote characters.
|
|Variations on the string run but do not return any records (See Examples 2
|and 3).
|
|The ultimate goal is to be able to build the SQL string using a Text Box
|similar to Example 4.
|
|Example 1
|
|"SELECT DISTINCT [tblPeople].[RegistrationID] FROM tblPeople WHERE
|((([tblPeople].[LastName]) Like "smi*"));"
|
|'1 - Does Not Work At All - Syntax Error: Copy From Working SQL Window
|
|Example 2
|
|"SELECT DISTINCT [tblPeople].[RegistrationID] FROM tblPeople WHERE
|((([tblPeople].[LastName]) Like ""smi*""));"
|
|'2 - Does Not Work As Desired - Returns zero records.
|
|Example 3
|
|"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
|((tblPeople.LastName) LIKE " & Chr(34) & "smi" & "*" & Chr(34) & ");"
|
|'3 - Does Not Work As Desired - Returns zero records.
|
|Example 4 - Actual goal
|
|"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
|(((tblPeople.LastName) LIKE '" & CStr(txtSearchValue.Value) & "*'));"
|
|'4 - Does Not Work As Desired - Returns zero records.
|
|Has anyone seen this before and do you know of a work around?
|
|Thanks,
|
|Rand E Gerald
|Database Specialist
|Information Services / Operations
|Bahá'í National Office
|1233 Central St.
|Evanston IL 60201
|(xxx) xxx-xxxx
|
|
Message #4 by "Gerald, Rand" <RGerald@u...> on Fri, 19 Jul 2002 16:03:47 -0500
|
|
Thanks to you Leo and Dave.
It turned out to be that the "%" wildcard was need by the ADO recordset
instead of the "*" wildcard. This was true even though I am using
linked
Access databases - we have yet to port this application to SQL Server.
It
would be nice if there were a toggle parameter for "Use Access
Wildcards"
vs. "Use SQL Wildcards" that could be used in the ADODB recordsets.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Leo Scott [mailto:leoscott@c...]
Sent: Friday, July 19, 2002 3:34 PM
To: Access
Subject: [access] RE: Problem with "LIKE" query in VBA
If you are using like with a string value enclose the like value in '.
Example:
SELECT LastName, FirstName FROM People WHERE LastName LIKE 'A*'
That is if you are using DAO. If you are using ADODB then substitute %
for
*.
|-----Original Message-----
|From: Gerald, Rand [mailto:RGerald@u...]
|Sent: Friday, July 19, 2002 1:10 PM
|To: Access
|Subject: [access] Problem with "LIKE" query in VBA
|Importance: High
|
|
|I am trying to create a "LIKE" query string in VBA.
|
|If I create the query in the Query window and run it, two records are
|returned.
|If I then go to the SQL window and copy the SQL text verbatim (See
Example
|1) it fails with a syntax error. Obviously caused by
misinterpretation of
|the doublequote characters.
|
|Variations on the string run but do not return any records (See
Examples 2
|and 3).
|
|The ultimate goal is to be able to build the SQL string using a Text
Box
|similar to Example 4.
|
|Example 1
|
|"SELECT DISTINCT [tblPeople].[RegistrationID] FROM tblPeople WHERE
|((([tblPeople].[LastName]) Like "smi*"));"
|
|'1 - Does Not Work At All - Syntax Error: Copy From Working SQL Window
|
|Example 2
|
|"SELECT DISTINCT [tblPeople].[RegistrationID] FROM tblPeople WHERE
|((([tblPeople].[LastName]) Like ""smi*""));"
|
|'2 - Does Not Work As Desired - Returns zero records.
|
|Example 3
|
|"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
|((tblPeople.LastName) LIKE " & Chr(34) & "smi" & "*" & Chr(34) & ");"
|
|'3 - Does Not Work As Desired - Returns zero records.
|
|Example 4 - Actual goal
|
|"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
|(((tblPeople.LastName) LIKE '" & CStr(txtSearchValue.Value) & "*'));"
|
|'4 - Does Not Work As Desired - Returns zero records.
|
|Has anyone seen this before and do you know of a work around?
|
|Thanks,
|
|Rand E Gerald
|Database Specialist
|Information Services / Operations
|Bah=E1'=ED National Office
|1233 Central St.
|Evanston IL 60201
|(xxx) xxx-xxxx
|
|
Message #5 by "Gregory Serrano" <SerranoG@m...> on Tue, 23 Jul 2002 19:18:57
|
|
Rand,
<< Example 4 - Actual goal
"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
(((tblPeople.LastName) LIKE '" & CStr(txtSearchValue.Value) & "*'));" >>
Try
"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
tblPeople.LastName LIKE '*" & Me.txtSearchValue & "*';"
* If the search box is text, no need to use CSTR.
* The .Value property should be unnecessary.
* The parentheses are confusing and uncessary in this case.
* You missed the wildcard in front of Me.txtSearchValue.
Greg
Message #6 by "Gerald, Rand" <RGerald@u...> on Wed, 24 Jul 2002 08:55:20 -0500
|
|
It turns out that with VBA and ADO you need to use the SQL Server
wildcards
instead of the Access wildcards.
All that was necessary was to replace the "*" with "%".
Thanks again,
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Gregory Serrano [mailto:SerranoG@m...]
Sent: Tuesday, July 23, 2002 2:19 PM
To: Access
Subject: [access] Re: Problem with "LIKE" query in VBA
Rand,
<< Example 4 - Actual goal
"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
(((tblPeople.LastName) LIKE '" & CStr(txtSearchValue.Value) & "*'));"
>>
Try
"SELECT DISTINCT tblPeople.RegistrationID FROM tblPeople WHERE
tblPeople.LastName LIKE '*" & Me.txtSearchValue & "*';"
* If the search box is text, no need to use CSTR.
* The .Value property should be unnecessary.
* The parentheses are confusing and uncessary in this case.
* You missed the wildcard in front of Me.txtSearchValue.
Greg
|
|
|
View Cart
