Can't get 'Not Like' to work

[tungmeister]

I've got the report as below. I'm trying to exclude devices that contain 'LAPTOP' or 'TABLET' within their name however they're still showing up in the report - not sure what I'm missing.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  ProcCapacity.CPU,
  ProcCapacity.MaxClockSpeed,
  tsysOS.OSname,
  tblAssetCustom.Location,
  tsysOS.OSname As OS,
  tblAssets.Username,
  tblADComputers.Description As Description1
From tblAssets
  Inner Join (Select tblAssets.AssetID,
    Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
    Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
    Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
    Max(tblProcessor.Architecture) As Architecture,
    Max(tblProcessor.Caption) As Name,
    Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
  From tblAssets
    Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Group By tblAssets.AssetID) As ProcCapacity On ProcCapacity.AssetID =
    tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where (tblAssets.AssetName Not Like '%LAPTOP%' And tblAssetCustom.State = 1) Or
  (tblAssets.AssetName Not Like '%TABLET%')
Order By ProcCapacity.MaxClockSpeed

[RCorbeil]

As you've presented it, you only care that the laptops are active. Is that the case? I.e. you want to filter out all tablets, regardless of their state and you want to filter out active laptops. Charles.X has assumed, and I'm going to assume the same, that you want to include active devices and you want to filter out laptops and tablets.

I'm going to offer a small twist on Charles.X's suggested

WHERE
  tblAssetCustom.State = 1
  AND (    tblAssets.AssetName NOT LIKE '%LAPTOP%'
       AND tblAssets.AssetName NOT LIKE '%TABLET%')

Or, since the parentheses are redundant because everything is being ANDed,

WHERE
  tblAssetCustom.State = 1
  AND tblAssets.AssetName NOT LIKE '%LAPTOP%'
  AND tblAssets.AssetName NOT LIKE '%TABLET%'

If you pull the NOT out, you can phrase it more in line with what it looks like you were thinking:

WHERE
  tblAssetCustom.State = 1
  AND NOT (   tblAssets.AssetName LIKE '%LAPTOP%'
           OR tblAssets.AssetName LIKE '%TABLET%')

[Esben_D]

I think your where clause is not correct.

Where (tblAssets.AssetName Not Like '%LAPTOP%' And tblAssetCustom.State = 1) Or
(tblAssets.AssetName Not Like '%TABLET%')

You'll have to use an And instead of an Or for the two names, removing all assets which have laptop in the name And all assets which have tablet in the name.

Where (tblAssets.AssetName Not Like '%LAPTOP%' And tblAssets.AssetName Not Like '%TABLET%') And tblAssetCustom.State = 1