Select query doesn't work in Access 2002

[Ken]

I have a select query in an Acc97 database that uses a
LIKE statement in a criteria expression when searching a
field with a word document linked to it, and it works fine.

If I open the database or convert the database to Access
2002, this query no longer works. All I get is an empty
table for a result (no error messages).

Is there a change in the way linked objects are
stored/handled in 2002? Or is there a syntax change I'm
unaware of?

I've tried using criteria other than LIKE statements and
it seems nothing works.

 

[John Viescas]

When you say "searching a field with a word document linked to it," do you
mean that the field is an OLE object, or is it text? If it is an OLE Object
data type, you should not be able to successfully apply criteria in a query.
If it's text (for example, the path to the Word document), then LIKE should
work the same in both 97 and 2002.

Please post the SQL from your query. Open the query in Design view, choose
SQL from the View menu, and copy and paste the text here.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Ken]

Here is the SQL:

SELECT DISTINCTROW tblQuestion.QuesNum,
tblQuestion.QuesText, tblQuestion.AnsShort,
tblClass.Class, tblClass.ExamType, tblQuesKA.KA
FROM tblQuestion INNER JOIN (tblQuesKA INNER JOIN tblClass
ON tblQuesKA.QuesNum = tblClass.QuesNum) ON
tblQuestion.QuesNum = tblClass.QuesNum
WHERE (((tblQuestion.QuesText) Like [KEYWORD (Use this
format: *KEYWORD*)]));

The field is an OLE object. The data type is set to OLE
Object in table design.

Based on my research of late, it maybe shouldn't work in
Acc97 either, but I didn't know any better at the time,
tried it and it works great. But now I really need the
same functionality in ACC2002

 

[John Viescas]

You're right. It shouldn't work in A97, either. You could try forcing it
like this:

WHERE CStr(tblQuestion.QuesText) Like [KEYWORD (Use this
format: *KEYWORD*)];

The CStr function should convert the OLE Object binary data to a string,
which should be searchable with LIKE. -- But it could be incredibly slow.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Here is the SQL:

SELECT DISTINCTROW tblQuestion.QuesNum,
tblQuestion.QuesText, tblQuestion.AnsShort,
tblClass.Class, tblClass.ExamType, tblQuesKA.KA
FROM tblQuestion INNER JOIN (tblQuesKA INNER JOIN tblClass
ON tblQuesKA.QuesNum = tblClass.QuesNum) ON
tblQuestion.QuesNum = tblClass.QuesNum
WHERE (((tblQuestion.QuesText) Like [KEYWORD (Use this
format: *KEYWORD*)]));

The field is an OLE object. The data type is set to OLE
Object in table design.

Based on my research of late, it maybe shouldn't work in
Acc97 either, but I didn't know any better at the time,
tried it and it works great. But now I really need the
same functionality in ACC2002

-----Original Message-----
When you say "searching a field with a word document linked to it," do you
mean that the field is an OLE object, or is it text? If it is an OLE Object
data type, you should not be able to successfully apply criteria in a query.
If it's text (for example, the path to the Word document), then LIKE should
work the same in both 97 and 2002.

Please post the SQL from your query. Open the query in Design view, choose
SQL from the View menu, and copy and paste the text here.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411



.

 

[Ken]

I changed the query to:
WHERE ((CStr(tblQuestion.QuesText) Like [KEYWORD (Use this
format: *KEYWORD*)]));

and the results are the same....empty table

You're right. It shouldn't work in A97, either. You could try forcing it
like this:

WHERE CStr(tblQuestion.QuesText) Like [KEYWORD (Use this
format: *KEYWORD*)];

The CStr function should convert the OLE Object binary data to a string,
which should be searchable with LIKE. -- But it could be incredibly slow.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Here is the SQL:

SELECT DISTINCTROW tblQuestion.QuesNum,
tblQuestion.QuesText, tblQuestion.AnsShort,
tblClass.Class, tblClass.ExamType, tblQuesKA.KA
FROM tblQuestion INNER JOIN (tblQuesKA INNER JOIN tblClass
ON tblQuesKA.QuesNum = tblClass.QuesNum) ON
tblQuestion.QuesNum = tblClass.QuesNum
WHERE (((tblQuestion.QuesText) Like [KEYWORD (Use this
format: *KEYWORD*)]));

The field is an OLE object. The data type is set to OLE
Object in table design.

Based on my research of late, it maybe shouldn't work in
Acc97 either, but I didn't know any better at the time,
tried it and it works great. But now I really need the
same functionality in ACC2002

-----Original Message-----
When you say "searching a field with a word document linked to it," do you
mean that the field is an OLE object, or is it text? If it is an OLE Object
data type, you should not be able to successfully apply criteria in a query.
If it's text (for example, the path to the Word document), then LIKE should
work the same in both 97 and 2002.

Please post the SQL from your query. Open the query in Design view, choose
SQL from the View menu, and copy and paste the text here.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
I have a select query in an Acc97 database that uses a
LIKE statement in a criteria expression when searching a
field with a word document linked to it, and it works fine.

If I open the database or convert the database to Access
2002, this query no longer works. All I get is an empty
table for a result (no error messages).

Is there a change in the way linked objects are
stored/handled in 2002? Or is there a syntax change I'm
unaware of?

I've tried using criteria other than LIKE statements and
it seems nothing works.


.

 

[John Viescas]

Try creating a query on tblQuestion and include a field -

StrOfOle: CStr(QuesText)

Take a look at it in Datasheet view and see if the results are at all
readable - can you "see" the individual words in the embedded document? You
might have to use a custom function to which you pass the OLE object. Use
GetChunk in the function to examine bits of the object and scan for the
search value.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

I changed the query to:
WHERE ((CStr(tblQuestion.QuesText) Like [KEYWORD (Use this
format: *KEYWORD*)]));

and the results are the same....empty table


"John Viescas" <[email protected]> wrote in message

You're right. It shouldn't work in A97, either. You could try forcing it
like this:

WHERE CStr(tblQuestion.QuesText) Like [KEYWORD (Use this
format: *KEYWORD*)];

The CStr function should convert the OLE Object binary data to a string,
which should be searchable with LIKE. -- But it could be incredibly slow.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Here is the SQL:

SELECT DISTINCTROW tblQuestion.QuesNum,
tblQuestion.QuesText, tblQuestion.AnsShort,
tblClass.Class, tblClass.ExamType, tblQuesKA.KA
FROM tblQuestion INNER JOIN (tblQuesKA INNER JOIN tblClass
ON tblQuesKA.QuesNum = tblClass.QuesNum) ON
tblQuestion.QuesNum = tblClass.QuesNum
WHERE (((tblQuestion.QuesText) Like [KEYWORD (Use this
format: *KEYWORD*)]));

The field is an OLE object. The data type is set to OLE
Object in table design.

Based on my research of late, it maybe shouldn't work in
Acc97 either, but I didn't know any better at the time,
tried it and it works great. But now I really need the
same functionality in ACC2002

-----Original Message-----
When you say "searching a field with a word document linked to it," do you
mean that the field is an OLE object, or is it text? If it is an OLE Object
data type, you should not be able to successfully apply criteria in a query.
If it's text (for example, the path to the Word document), then LIKE should
work the same in both 97 and 2002.

Please post the SQL from your query. Open the query in Design view, choose
SQL from the View menu, and copy and paste the text here.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
I have a select query in an Acc97 database that uses a
LIKE statement in a criteria expression when searching a
field with a word document linked to it, and it works fine.

If I open the database or convert the database to Access
2002, this query no longer works. All I get is an empty
table for a result (no error messages).

Is there a change in the way linked objects are
stored/handled in 2002? Or is there a syntax change I'm
unaware of?

I've tried using criteria other than LIKE statements and
it seems nothing works.


.