Like query (1 Viewer)

[stavross]

I am trying to construct a query that searches for a character strings in any position in a column.

The problem lies in the fact that the character strings I am searching for can be any of 740 values.

I initially started to key in the 740 values in the criterea box, but this displays an error when more than 1024 characters are keyed into criterea.

I then tried to key the commands free format in the SQL view. This accepted the statement, but when I tried to run it, it displays the message 'Query is too complex'.

An example of the select statewment is:-

SELECT TBL1.ABC, TBL1.DEF, TBL1.GHI,
FROM TBL1
WHERE ([TBL1].[ABC] Like "*3010000*" Or [TBL1].[ABC] Like "*3010002*" ([TBL1].[ABC] Like "*3531965*"); (The Like and Or continue 740 times).

Is there another way of obtaining the desired results??

 

[The_Doc_Man]

Well, there is an ugly way of doing it, I guess. Here's how I would take this on.

Make a table in which you will list the 740 different strings you will seek, one string per record. Maybe that string is the only field you need in this table. Call the field SRHVAL (search-value). Just for the sake of formality, you could make that field a primary key, but in this case it really wouldn't matter that much.

Now create a new query that does a pseudo-join. Go to SQL view to do it.

SELECT TBL1.PRIMEKEY FROM TBL1, MATCHLIST WHERE TBL1.ABC LIKE "*" & MATCHLIST.SRHVAL & "*" ;

(I'm assuming you have a primary key field that isn't being searched, though it was just a name I made up for the simple-case example I'm using.)

The result is the list of keys of all TBL1 entries that matched any MATCHLIST.SRHVAL entry. I tried this query in AC2K before I showed you the method and it worked on one of my uglier tables, so I think it should work OK for you.

Now, the down sides of this method. If the .ABC field is such that it sometimes has more than one match, the same record will appear twice in the pseudo-join. I.e., duplicate "primary" keys. So whatever you do has to take this into account unless you are absolutely CERTAIN that multiple matches never happen. (Even then, I would test for the possibility just to be safe.)

Another issue is that this is not a fast query because of the "LIKE" clause. In fact, it will be slightly slower than molasses in Canada in January if your targeted table is big enough.

You can add other items to the SELECT clause if you need them. I just showed you the minimum methodology that will get the match-ups done. In fact, once you get the basic query working, switch to the query grid and add the other fields you need to see. Once the basic query is done, embellish it all you want.

 

[Pat Hartman]

The only reasons for using the Like operator are that you are supplying a partial value or the field you are searching contains multiple strings. Which is your situation and why would you want to search for 740 values at once?

 

[stavross]

I don't mind ugly so long as it works, which it does.

Thanks for your Help.