Querying a Memo field using a variable string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am rewriting a lot of old web pages that used dynamic SQL in the code of
the page and converting them to use stored procedures in the database
instead. I am having difficulty converting a specific query to work in
Access 2007. The query as it is written in the page is:

SELECT Product.Prod_Pk FROM Product WHERE (((Product.Prod_Title) LIKe
'"&text&"'))

When I try to create the query in Access I wrote it like this:

SELECT Product.Prod_Pk FROM Product WHERE (((Product.Prod_Title) LIKe [text]))

I run the query within Access and it asks me for the variable but when I
enter something I know to be in the database the query returns no rows. What
am I missing?

Donovan
 
Based on the original query, it doesn't look like you really want to use the
LIKE operator at all. Of course it appears that "text" in the original query
was a variable, so it would be important to look at the line of the original
code that defines that variable. I'm going to assume that you want to find
all of the Produce_PK values where the "text" is contained anywhere within
the Prod_Title field. In that case, try:

SELECT Product.Prod_Pk
FROM Product
WHERE Product.Prod_Title LIKe "*" & [text] & "*"

That should run OK from within an Access query, because it will ask you for
the "value" of [text], but if you are going to do this as some form of
database backend for a web page, you are going to have to either pass the
value of [text] to a stored procedure, or write it into a table.

HTH
Dale
 
Dale,

Thanks, that worked perfectly.

Dale Fye said:
Based on the original query, it doesn't look like you really want to use the
LIKE operator at all. Of course it appears that "text" in the original query
was a variable, so it would be important to look at the line of the original
code that defines that variable. I'm going to assume that you want to find
all of the Produce_PK values where the "text" is contained anywhere within
the Prod_Title field. In that case, try:

SELECT Product.Prod_Pk
FROM Product
WHERE Product.Prod_Title LIKe "*" & [text] & "*"

That should run OK from within an Access query, because it will ask you for
the "value" of [text], but if you are going to do this as some form of
database backend for a web page, you are going to have to either pass the
value of [text] to a stored procedure, or write it into a table.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Donovan said:
I am rewriting a lot of old web pages that used dynamic SQL in the code of
the page and converting them to use stored procedures in the database
instead. I am having difficulty converting a specific query to work in
Access 2007. The query as it is written in the page is:

SELECT Product.Prod_Pk FROM Product WHERE (((Product.Prod_Title) LIKe
'"&text&"'))

When I try to create the query in Access I wrote it like this:

SELECT Product.Prod_Pk FROM Product WHERE (((Product.Prod_Title) LIKe [text]))

I run the query within Access and it asks me for the variable but when I
enter something I know to be in the database the query returns no rows. What
am I missing?

Donovan
Click to expand...
Click to expand...
 
Back
Top