Like query works in query editor but not in VBA code

[Nates]

If i use the following in the query editor it returns rows from my database, however when i run the very same query in VBA the recordset recordcount returns -1 every time:

sql = "SELECT uniqueid, Notes FROM TaskRecords WHERE Notes Like '*" & SearchBox.Value & "*'"
UserRS.Open sql, UserConn

The [Notes] field is type Memo. The database contains around 5500 entries at present with up to 1000 chars in each memo field. I am using the above query to speed up the search form i put together (which had previously used InStr record by record to find matches - extremely slow).

Any ideas?

Thanks

[MikeTheBike]

If i use the following in the query editor it returns rows from my database, however when i run the very same query in VBA the recordset recordcount returns -1 every time:

sql = "SELECT uniqueid, Notes FROM TaskRecords WHERE Notes Like '*" & SearchBox.Value & "*'"
UserRS.Open sql, UserConn

The [Notes] field is type Memo. The database contains around 5500 entries at present with up to 1000 chars in each memo field. I am using the above query to speed up the search form i put together (which had previously used InStr record by record to find matches - extremely slow).

Any ideas?

Thanks

Hi

If you are sure there are records to be return for the criteria used, then perhaps this will yield a RecordCount > 1

UserRS.Open sql, UserConn, adOpenStatic, adLockReadOnly

???

With the parameters you have (not) used or with a dymamic recordsets you should use the recordset .EOF property to test that records have been returned.

MTB

[Nates]

Unfortunately the above did not find any records either.

Ive uploaded a couple of screenshots that highlight the problem:

http://www.wow-pickup.com/images/ErrorCode.JPG

http://www.wow-pickup.com/images/ErrorFrontEnd.JPG

http://www.wow-pickup.com/images/QuerySQL.JPG

http://www.wow-pickup.com/images/QueryResult.JPG

The above SQL in the query editor is slightly adapated but produced the same problem when used in the code.

Im baffled. Seems like a bug.

[MikeTheBike]

Hi again

Unfortunately, I only gave you half the answer last time. Failed to note this is an ADO recordset, so the wild card characture is % NOT *.

So you need my previous mod plus the above change, ie.

sql = "SELECT uniqueid, Notes FROM TaskRecords WHERE Notes Like '%" & SearchBox.Value & "%'"
UserRS.Open sql, UserConn, adOpenStatic, adLockReadOnly

??

MTB

[Nates]

Thanks Mike that did the trick :)

[NeoPa]

You may find ANSI Standards in String Comparisons interesting.