I have an access 2013 database. I am building a multiple field search form. I am adding my like statements to the where clause. The first field works great, When no criteria is requested it return all records. Second field drops a record, the third field drops 48 records when no criteria is added etc.
SQL Statement below
SELECT CorrespondenceTracking.ID, CorrespondenceTracking.DateReceived,
CorrespondenceTracking.DateDue, CorrespondenceTracking.DocumentType,
CorrespondenceTracking.FirstName, CorrespondenceTracking.LastName,
CorrespondenceTracking.Organization, CorrespondenceTracking.DateCompleted,
CorrespondenceTracking.Description, CorrespondenceTracking.AssigneeAFirstName,
CorrespondenceTracking.AssigneeALastName, CorrespondenceTracking.DivisionA,
CorrespondenceTracking.DateDivisionReceived, CorrespondenceTracking.DateDOHQClosed,
CorrespondenceTracking.DateDivisionAssignee, CorrespondenceTracking.DateSentToRequestor,
CorrespondenceTracking.DateToDOHQ, CorrespondenceTracking.DateExtension,
CorrespondenceTracking.DateResponse, CorrespondenceTracking.Status,
CorrespondenceTracking.SDT, CorrespondenceTracking.Costs,
CorrespondenceTracking.Action, CorrespondenceTracking.Comments,
CorrespondenceTracking.DeputyComments, CorrespondenceTracking.ResponseMethod,
CorrespondenceTracking.MailingAddress, CorrespondenceTracking.Area,
CorrespondenceTracking.EmailAddress, CorrespondenceTracking.[AssigneeBFirst Name],
CorrespondenceTracking.[AssigneeB LastName], CorrespondenceTracking.DivisionB,
CorrespondenceTracking.AssigneeCFirstName, CorrespondenceTracking.AssigneeCLastName,
CorrespondenceTracking.DivisionC, CorrespondenceTracking.AssigneeDFirstName,
CorrespondenceTracking.AssigneeDLastName, CorrespondenceTracking.DivisionD,
CorrespondenceTracking.AssigneeEFirstName, CorrespondenceTracking.ADApproval
FROM CorrespondenceTracking
WHERE (((CorrespondenceTracking.ID) Like "*" & [Forms]![SearchForm]![ID] & "*")
AND ((CorrespondenceTracking.DateReceived) Like "*" & [Forms]![SearchForm]![DateReceived] & "*")
AND ((CorrespondenceTracking.DateDue) Like "*" & [Forms]![SearchForm]![DateDue] & "*")
AND ((CorrespondenceTracking.DocumentType) Like "*" & [Forms]![SearchForm]![DocumentType] & "*")
AND ((CorrespondenceTracking.FirstName) Like "*" & [Forms]![SearchForm]![FirstName] & "*")
AND ((CorrespondenceTracking.LastName) Like "*" & [Forms]![SearchForm]![LastName] & "*")
AND ((CorrespondenceTracking.Organization) Like "*" & [Forms]![SearchForm]![Organization] & "*")
AND ((CorrespondenceTracking.DateCompleted) Like "*" & [Forms]![SearchForm]![DateCompleted] & "*")
AND ((CorrespondenceTracking.Description) Like "*" & [Forms]![SearchForm]![Description] & "*")
AND ((CorrespondenceTracking.AssigneeAFirstName) Like "*" & [Forms]![SearchForm]![AssigneeAFirstName] & "*")
AND ((CorrespondenceTracking.AssigneeALastName) Like "*" & [Forms]![SearchForm]![AssigneeALastName] & "*"));
