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

 

[pmmgpgp]

I think that this might help.

I like the intstr command.

You might try something like the following

WHERE instr(fieldname,"!&quot > 0

Dave

 

[Lysander]

Thank you pmmgpgp

I just spent 3 long hours trying to figure out why my Query with 'like' worked perfectly in Access and complely failed in VB.

the instr works perfectly in both.

 

[CCLINT]

While using Jet VBA (VbaJet.DLL) in a SELECT statement is possible, three problems arise:

1. It is provider specific, meaning you'll be in for a surprise if you change to another provider.

2. The query runs much slower.

3. The query is not optimized.

Of course, if there is not other possibility, then it may be best to design the statement so that a change is made easily, or use stored proceedures (MDB stored queries).
Then a change is easily made only by changing the the source of the proceedure (query) and not the source code.

But, of course no answers were posted except the above using the InStr function - which works.

But, for the sake of others, try using a stored query and let that be provider specific instead of your code.

The solution to the above problem may be solve using the following syntax:

WHERE fieldname Like '%[ !]%'

Notice the space between the left bracket and the exclaimation sign. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!