Incomplete records returned - too many "like" criteria?

[CGBJR]

I have a small table (about 8000 records) linked to an even smaller table (1200 records). I'm building a query that I want to have prompted "like" criteria in four text fields. The problem is that when I use three "likes", I get all the proper records, but when I add the fourth, I get fewer records.

This happens no matter what criteria I enter in any or all prompts, or even if I enter criteria for only one prompt. I thought my syntax in the criteria might be bad, so I made the fourth criteria a non-prompted one: like "*" Even that gave me incomplete results.



Is there a limit on the number of criteria I can use? I've compacted and repaired the db, and made sure there are no field names with spaces in them. I'm stumped.

Access 2010, Windows 7 64 bit home edition.

Thanks in advance - Chuck B

[orange]

Please post the SQL of your query.

[CGBJR]

Please post the SQL of your query.

I did not generate this SQL directly (just filled in the query in design mode) but here it is:

SELECT DISTINCTROW CD_TRACKS.ShelfNum, COMPOSER.Lastname, CD_TRACKS.Performer, CD_TRACKS.Original_Title, CD_TRACKS.English_Title, CD_TRACKS.Date_of_Composition, CD_TRACKS.TrackNum, CD_TRACKS.Duration_of_Work, CD_TRACKS.Conductor, CD_TRACKS.Genre, CD_TRACKS.Composer_Add, CD_TRACKS.[New ID], CD_TRACKS.Composer_New
FROM CD_TRACKS INNER JOIN COMPOSER ON CD_TRACKS.Composer_New = COMPOSER.[Composer#]
WHERE (((CD_TRACKS.ShelfNum)>1) AND ((COMPOSER.Lastname) Like "*" & [All of Part of the composer's last name:] & "*") AND ((CD_TRACKS.Performer) Like "*" & [AOP of PERFORMER NAME: ] & "*") AND ((CD_TRACKS.Original_Title) Like "*" & [AOP of Title in Original Language:] & "*") AND ((CD_TRACKS.Composer_New) Is Not Null))
ORDER BY CD_TRACKS.ShelfNum;

[orange]

I don't think you are reaching a limit on the number of LIKES.
You are ANDing all of your criteria so you are getting a narrower and narrower set of data.
Also you are using LIKE *sometext* which means these characters in any part of the field value.

You could try Like sometext* where you know characters at the start of the field.

Can you finnnd a few examples of records that would meet all of your criteria, or set up some tests; then add more LIKEs to see if there are no records matching all of your criteria; or prove it is something to do with the number of LIKES.

[CGBJR]

As far as I can tell, adding the like "*" language chooses everything in that column. Am I wrong? My point is, no matter what the 4th "like" is, I get incomplete data. 47 records without the like "*", 33 with it. Now if instead of the like command I use something like >"a" as the 4th selector I get all the records. It's just the like command that messes things up.