Another saved parameter query question (MSAccess XP)

[Larry Rekow]

Trying to retrieve a recordset using a saved parameter query in
Access. the query is named "resultsq1x"

works fine when i run it within access:

SELECT Results.ref, Results.commodity, IMPACTWEB.Field5,
IMPACTWEB.Field20
FROM IMPACTWEB RIGHT JOIN Results ON Results.ref=IMPACTWEB.Field5
WHERE (Results.ref=[searchterm] Or IMPACTWEB.Field20 LIKE "*" &
[searchterm] & "*");


I call this query from an ASP page like so:

searchterm = Request.Form("blah")
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open import_entry
Set RS = Server.CreateObject("ADODB.Recordset")
objConnection.resultsq1x searchterm, RS
Do While Not RS.EOF
Response.Write(RS("ref"))
Response.Write(RS("Field20"))
Response.Write("<BR>")
RS.MoveNext
Loop

When I run it from a browser (after inputting the searchterm), It will
return correctly if I put in a "ref" number that exists.

However, it will not return any records if I put in a string that
exists in the "Field20" field of a record.

If I modify the query and change: LIKE"*" & [searchterm] & "*"
to just: LIKE [searchterm]

then the query works correctly, returning record(s) if there is a
string that equals the "ref" field, or if the string matches a
complete string in the "Field20".

Why can't i use the LIKE "*" & [searchterm] & "*" when it works
perfectly run from within Acess?? I want to search on fields that
just might CONTAIN the string.

Thanks,

Larry Rekow
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

[Bob Barrows [MVP]]

Larry Rekow wrote:

> Trying to retrieve a recordset using a saved parameter query in
> Access. the query is named "resultsq1x"
>
> works fine when i run it within access:
>
> SELECT Results.ref, Results.commodity, IMPACTWEB.Field5,
> IMPACTWEB.Field20
> FROM IMPACTWEB RIGHT JOIN Results ON Results.ref=IMPACTWEB.Field5
> WHERE (Results.ref=[searchterm] Or IMPACTWEB.Field20 LIKE "*" &
> [searchterm] & "*");
>
>
> I call this query from an ASP page like so:
>
> searchterm = Request.Form("blah")
> Set objConnection = Server.CreateObject("ADODB.Connection")
> objConnection.Open import_entry
> Set RS = Server.CreateObject("ADODB.Recordset")
> objConnection.resultsq1x searchterm, RS
> Do While Not RS.EOF
> Response.Write(RS("ref"))
> Response.Write(RS("Field20"))
> Response.Write("<BR>")
> RS.MoveNext
> Loop
>
> When I run it from a browser (after inputting the searchterm), It will
> return correctly if I put in a "ref" number that exists.
>
> However, it will not return any records if I put in a string that
> exists in the "Field20" field of a record.
>
> If I modify the query and change: LIKE"*" & [searchterm] & "*"
> to just: LIKE [searchterm]
>
> then the query works correctly, returning record(s) if there is a
> string that equals the "ref" field, or if the string matches a
> complete string in the "Field20".
>
> Why can't i use the LIKE "*" & [searchterm] & "*" when it works
> perfectly run from within Acess?? I want to search on fields that
> just might CONTAIN the string.
>

I know it's counterintuitive, but when calling a query, even a saved query,
via ADO, you have to use the ODBC wildcards (& and _) instead of the Jet
wildcards (* and ?) in your LIKE comparisons.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Larry Rekow]

On Wed, 4 Aug 2004 22:23:44 -0400, "Bob Barrows [MVP]"
<reb01501@NOyahoo.SPAMcom> wrote:

>I know it's counterintuitive, but when calling a query, even a saved query,
>via ADO, you have to use the ODBC wildcards (& and _) instead of the Jet
>wildcards (* and ?) in your LIKE comparisons.
>
>Bob Barrows

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++
counterintuitive INDEED :)

it worked after I changed the query in Access to:

SELECT Results.ref, Results.commodity, IMPACTWEB.Field5,
IMPACTWEB.Field20
FROM IMPACTWEB RIGHT JOIN Results ON Results.ref=IMPACTWEB.Field5
WHERE (Results.ref=[searchterm] Or IMPACTWEB.Field20 Like "%" &
[searchterm] & "%");

sheesh. Thanks a ton. (isn't there a page that describes all of the
varying versions of wildcards, et al? I seem to have misplaced it.)

You would think I would know all this stuff about wildcards since,
when coding, i'm always saying stuff like: @#&@$#!%

Larry Rekow
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."