Using OLEDB Find on a substring...

[NatHunter]

Folks,

I decided to convert my ODBC db to OLEDB and therefore use OLEDB recordsets.

I have changed my 'findfirsts' to 'Find',but I receive an error from the following line:

rsBetOnA.Find "Left([BookCategory]," & Len(BookCategory) & &quot = '" & BookCategory & "' , , adSearchForward, 1

- OLEDB doesn't seem to like my 'LEFT' statement.

Any ideas would be much appreciated !

 

[petermeachem]

I suspect you need to use substring. Just think sql server.

The SUBSTRING function returns a portion of a character or binary string.
The SUBSTRING function has the following syntax:
SUBSTRING(expression, start, length)
SUBSTRING always takes three parameters. The first can be a character or binary string, a column name, or a string-valued expression that includes a column name. The second parameter specifies the position at which the substring should begin. The third specifies the length (in number of characters) of the string to be returned.
This example displays the last name and first initial of each author ¾ for example, Bennet A:
SELECT au_lname, SUBSTRING(au_fname, 1, 1)
FROM authors

Here's how to display the second, third, and fourth characters of the string constant abcdef:
SELECT x = SUBSTRING('abcdef', 2, 3)
Peter Meachem
peter @ accuflight.com

 

[NatHunter]

Thanks for your help. It does not seem to be the answer though. The following is the error I'm getting, either using LEFT or SUBSTRING:

"Arguments are of the wrong type, are out of acceptable range or are in conflict with one another".

I'm using 'FIND' from a standard VB project.

Paul