SQL WHERE statement only working on some rows-CodePudding

I have made a SQL statement to filter projects and are having a problem with only filtering the active projects. It works on some of the data but few rows are not affected.

When I try to filter all of the active project I have used a varchar(1) as a true/false boolean 1=completed 0=active

SQL:

SELECT 
    dbo_tblTrialTest.ID, 
    dbo_tblTrialTest.PartNumber, 
    dbo_tblTrialTest.CreationDate, 
    dbo_tblTrialTest.Init, 
    dbo_tblTrialTest.CompletionInit, 
    dbo_tblTrialTest.CustomerName, 
    dbo_tblTrialTest.CompletionDate, 
    dbo_tblTrialTest.Description, 
    dbo_tblTrialTest.ActiveAsString
FROM
    dbo_tblTrialTest
WHERE 
    (((dbo_tblTrialTest.ActiveAsString) = TRIM("0"))) 
    AND ((dbo_tblTrialTest.ID) LIKE '*' & [Forms]![frmTrialTestOverview]![tfFilter] & '*') 
     OR (((dbo_tblTrialTest.PartNumber) LIKE '*' & [Forms]![frmTrialTestOverview]![tfFilter] & '*')) 
     OR (((dbo_tblTrialTest.CustomerName) LIKE '*' & [Forms]![frmTrialTestOverview]![tfFilter] & '*'));

Output from this query:

So only some rows are not affected by the ActiveAsString which are rows that data are entered into? I can't really seem to think why this is the case.

CodePudding user response：

It makes no sense to trim "0". Could be that the field need it, however:

Trim(dbo_tblTrialTest.ActiveAsString)="0"

But do use a bit field for True/False values.

Also, try this logic:

WHERE 
    dbo_tblTrialTest.ActiveAsString = "0" 
    AND 
    (dbo_tblTrialTest.ID LIKE '*' & [Forms]![frmTrialTestOverview]![tfFilter] & '*' 
    OR dbo_tblTrialTest.PartNumber LIKE '*' & [Forms]![frmTrialTestOverview]![tfFilter] & '*' 
    OR dbo_tblTrialTest.CustomerName LIKE '*' & [Forms]![frmTrialTestOverview]![tfFilter] & '*');