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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rockiroads
u thought of using varchar or nvarchar as the field type instead of text?
ASKER
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
wots your current query look like now?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.