Unexpected results with added criteria

[Jeff_Henze]

I’m seeing an unexpected behavior in a report. When I add an additional criterion, instead of dropping 1 record like I expect, it drops almost all of them. The report is to show old systems to be replaced, and I want to drop out those I’ve tagged as “retired”. I’m using Custom2 (which I call “Status”) to hold an assignment status (Assigned, Stock, Retired, etc. ). As of yet, I only have data in the Custom2 field for 3 assets: “Unassigned”, “Secondary”, and “Retired” so in my test, only one record should drop off my report.
When I run it with [Not Like '%retired%'] in the criteria, I get only 6 results (the “Unassigned”, the “Secondary”, and 4 blanks). Remove the [Not Like '%retired%'] and I get 614 results.

Without the [Not Like '%retired%'] – 614 results:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblState.Statename,
  tblADComputers.Location,
  tblAssets.Description,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssetCustom.Custom3 As Company,
  tblAssetCustom.Custom2 As Status
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
    tblComputersystem.Domainrole
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Manufacturer Like '%dell%' And
  tblADComputers.OU Not Like '%waltham%' And tsysAssetTypes.AssetTypename =
  'Windows' And tblDomainroles.Domainrole < 2
Order By tblAssetCustom.PurchaseDate

With the [Not Like '%retired%'] – 6 results:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblState.Statename,
  tblADComputers.Location,
  tblAssets.Description,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssetCustom.Custom3 As Company,
  tblAssetCustom.Custom2 As Status
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
    tblComputersystem.Domainrole
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Custom2 Not Like '%retired%' And
  tblAssetCustom.Manufacturer Like '%dell%' And tblADComputers.OU Not Like
  '%waltham%' And tsysAssetTypes.AssetTypename = 'Windows' And
  tblDomainroles.Domainrole < 2
Order By tblAssetCustom.PurchaseDate

Any ideas why I would get these unexpected results?

[Jeff_Henze · Accepted solution]

Answered my own question after a lot of playing around...

Apparently [Not Like '%retired%'] means
* items that do not have something like 'retired' in them
* items that are not NULL

So any fields with a null value in them were also being excluded (many fields in my db do not have any value in that particular field). So I changed:

Where tblAssetCustom.Custom2 Not Like '%retired%' And
tblAssetCustom.Manufacturer Like '%dell%' And tblADComputers.OU Not Like
'%waltham%' And tsysAssetTypes.AssetTypename = 'Windows' And
tblDomainroles.Domainrole < 2

To be...

Where (tblAssetCustom.Custom2 Not Like '%retired%' Or
tblAssetCustom.Custom2 is NULL) And
tblAssetCustom.Manufacturer Like '%dell%' And tblADComputers.OU Not Like
'%waltham%' And tsysAssetTypes.AssetTypename = 'Windows' And
tblDomainroles.Domainrole < 2

which returns all the expected rows.
-Jeff