Addition of "Like" criteria into query results in records with said criteria as null to not appear

[WesHarding]

Hi Everyone,

My issue is confusing enough to make a title for, let alone explain, so please be patient with me.

I have a search form, that is comprised of a number of text boxes. Like many search forms, I want one to type in a name to recieve results from my query. I have just realized that when I add any criteria to my query to filter my records, they end up not detecting some records (they no longer appear in the query result that should show all records). Further more, these records that are not appearing are ones that don't have any information in the field that the criteria is designed to filter.

Have any of you ever come across this before?

I originally thought this was an AND or OR issue, but it's occuring with only one criteria!! SOS!

SQL for query:
SELECT
tblWHO.FarmName,
tblWHO.FarmCivicAddress,


tblWHO.FarmCommunity,
tblWHO.VERIFIED,
tblWHO.Phone1C1,
tblWHO.Phone2C1,
tblWHO.Email,
tblWHO.Website,
tblWHO.Facebook,
tblWHO.Notes,
tblWHO.FarmerID,
tblContacts.FirstName1,
tblContacts.LastName1,
tblWHO.Municipality_WHO
FROM tblWHO LEFT JOIN tblContacts ON tblWHO.FarmerID = tblContacts.FarmerContactID
WHERE (((tblWHO.FarmCommunity) Like "*" & [Forms]![MainSearchForm]![txtcommunity] & "*"));

Wesley

[rpeare]

Your query as written will:

1. If txtcommunity is blank, return all records where FarmCommunity is not null (essentially like "*") KEEP IN MIND - this may find strings with no printable characters like line feeds
2. if txtcommunit is NOT blank, return all records matching the typed string (like "*test*")

if you want to show blank records when you leave txtcommunity blank you may need to do something other than test for null

i.e.

WHERE (((tblWHO.FarmCommunity) Like "*" & [Forms]![MainSearchForm]![txtcommunity] & "*") AND len(trim([farmcommunity])) > 0);