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?