Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Multiple Criteria To Exclude If Certain Text String Found

Posted by: bakersburg9 Sep 10 2019, 11:40 AM

I have a query that pulls in a field called “Job Name” and another field called "Description" – I’m trying to EXCLUDE from the results of my query anything that has the string “do not use” in the description, and also exclude any records that contain the text string “Rcls” – works great – but there are a small number of records that contain the text string “Reclass” – I swear I’ve tried everything as far as the Design View of the query on the Criteria line, and the “Or:” line – nothing works – I’ve tried putting "Note Like *Do not use*"" in the criteria for Job Name and below it on the “Or:” line and also not on the Or: line – every possible combination – plus I’ve tried: “Not In("*Rcls*" And Not Like "*reclass*") everything

CODE
SELECT CostZeroBilling_Q1toQ3_2017_19.Job, CostZeroBilling_Q1toQ3_2017_19.[Job Name], CostZeroBilling_Q1toQ3_2017_19.Description, CostZeroBilling_Q1toQ3_2017_19.amount FROM CostZeroBilling_Q1toQ3_2017_19 INNER JOIN LostJobsWithJobCost_2016_2019 ON CostZeroBilling_Q1toQ3_2017_19.Job = LostJobsWithJobCost_2016_2019.[Job Number]
WHERE (((CostZeroBilling_Q1toQ3_2017_19.[Job Name]) Not Like "*Do not use*") AND ((CostZeroBilling_Q1toQ3_2017_19.Description) Not Like "*Rcls*"));

Any help would be appreciated...

Posted by: arnelgp Sep 10 2019, 12:06 PM

CODE
SELECT CostZeroBilling_Q1toQ3_2017_19.Job, CostZeroBilling_Q1toQ3_2017_19.[Job Name], CostZeroBilling_Q1toQ3_2017_19.Description, CostZeroBilling_Q1toQ3_2017_19.amount
FROM CostZeroBilling_Q1toQ3_2017_19 INNER JOIN LostJobsWithJobCost_2016_2019 ON CostZeroBilling_Q1toQ3_2017_19.Job = LostJobsWithJobCost_2016_2019.[Job Number]
WHERE (((CostZeroBilling_Q1toQ3_2017_19.[Job Name]) Not Like "*Do not use*") AND ((InStr(1,[description],"Rcls"))=0) AND ((InStr(1,[description],"Reclass"))=0));