Like Criteria is not filtering like it's supposed to be

[Ron Mowry]

I have a linked table I am attempting to filter data out of. One of my field has several acronyms and I am looking only for 1 specificly "XTL" When I use the like "XTL" in the criteria field it is not filtering out all the other acrynyms in actuallity it is doing nothing at all. This table is a linked ODCB connection will this affect filtering using the like statement?

[nico5038]

Try:

like "%XTL%"

And be sure that the acronym is in Capitals...

Nic;o)

[Ron Mowry]

I tried that and it did not filter anything. I am a bit confused as to why this is not working. I am no guru in Access however I have used that function many times before and I am at a loss as to why this is not working.

[nico5038]

Hmm, you could try to use a PassThrough query. Change the query type to PassThrough and enter a select statement in the SQL-language of the ODBC database.
When it's e.g. Oracle you'll need % as wild character.

Nic;o)

[colintis]

If you are using the query within Access, try using * instead of % and ' instead of "

like '*XTL*'

This is the M$ standard...i hate to say this b'cause M$ change alot of SQL standard function characters to another things we need to discover....

[NeoPa]

Ron, Your question is particularly unclear. You say you are looking for the value 'XTL' yet you say you are using the Like construct. You have also not posted any SQL at all, which may have given us a chance to work out what your question should have been.

If you are looking for values of precisely 'XTL' then simply use = in your WHERE clause. If you are looking for any value with 'XTL' embedded within it then use '*XTL*' in most cases, but '%XTL%' if you have ANSI-92 compatibility set in your database options.

If you continue to have problems then please post your existing SQL and explain clearly what is going wrong.

[NeoPa]

Colintis:
This is the M$ standard...i hate to say this b'cause M$ change alot of SQL standard function characters to another things we need to discover....

Not entirely true. The * is simply an older standard that MS still support, but they do provide the option of compatibility with the later standard (%). The quotes issue is MS as far as I've been able to tell. It's more about making it nooby-friendly than any desire to trip up standards though. Frustrating at times, but there is actually solid reasoning behind it, whether we agree with that reasoning or not. See ANSI Standards in String Comparisons for more on this.