SQL Server, Access 2003 Text field data search

How to search a text field in an ODBC pull of data into access.

I have a database on server 2003.  Table X has Field Y which is in the "Text" format.

When I try to do a query on this table and search for the word "test" I get the message:
ODBC -- call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL and LIKE operator.

I do have access to SQL Server Enterprise Manager and could use the query analyzer if given the correct syntax. I know enough about SQL Server to not mess with it unless given the correct syntax.

I need to pull every record with the word "test" in Field "Y" from date range (using field "Created") 1/1/2007 and 7/1/2007.    I need to see the rest of the text in the field where the word "test" is present.
LVL 9
Sean MeyerAsked:
Who is Participating?
 
GRayLCommented:
Try this:

SELECT * FROM X WHERE Y LIKE '%TEST%' AND Created <= '1/1/2007" and Created <='7/1/2007'
0
 
GRayLCommented:
0
 
rockiroadsCommented:
u thought of using varchar or nvarchar as the field type instead of text?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Sean MeyerAuthor Commented:
I have no control over the design of the SQL database.  It is my companies main application supported by one company and created by another.  Any changes to the application take a minimum of 6 months and have to be approved by committee.  (read never happen)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the LIKE keyword in this manner:

SELECT * FROM SomeTAble WHERE Y LIKE '%TEST%'

This would return ALL records with the characters TEST in it, regardless of whether it the word "test", "detest", etc etc ...
0
 
rockiroadsCommented:
wots your current query look like now?
0
 
GRayLCommented:
Thanks, glad to help.  Truth be know, I went to the URL I provided earlier, found the 'translations' for Like and date betweens and composed the query above.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.