ado "Like" Query

[Niv3k]

I am running a query on an Access table through VB with ADO. Basically I am looking for an occurance of an exclamation point (!) anywhere in a text field. So I use the SQL:
WHERE fieldname LIKE '%[!]%'
I have also tried:
WHERE fieldname LIKE '%!%'

Every other time I run the query, it pulls up all the records in the table. Then I run it again, it works right. When I run it again, it pulls up all the records in the table, etc....

Does anyone have any ideas? If I run this in the query editor in Access, it always works right, but then I have to use * instead of %. But through ADO, it doesn't recognize * as the wildcard.

Please help, any ideas will be appreciated.

Kevin

 

[cmmrfrds]

What you have will probably not work. You must be running a ADO query every other time (check your logic flow). There are a number of things you need to consider.

1. Not a digit [!0-9] , the ! is a reserved symbol in the wildcard search.
2. You are probably going back and forth between DAO and ADO recordsets (queries).
3. Access 2000 mdb defaults to DAO, and Access 2000 adp defaults to ADO. So, using query builder in an mdb gives you a DAO query.
4. The wildcard characters are different in ADO and DAO. ADO uses % and DAO uses *.

Use a function to find the !.
Check out the Instr function.
Something like the following.
where Instr(1, myfield, "!&quot > 0