Filter syntax versus criteria syntax in Report Builder

[treichert]

I'm working in Report Builder and was wondering when I run a report and I put %-% in a column filter I will get results. If I try and put the same thing in the criteria box for the same field while editing the report, it says 'This report has no results!' when I run the report. This happens in any report - existing or one I build.

Thanks for any input!

[Daniel_B · Accepted solution]

The wildcard only works together with the "Like" operator. You need to replace "=" with "Like".

Select Top 1000000 tblAssetCustom.AssetID,
 tblADusers.Description,
 tblADusers.Firstname,
 tblADusers.Lastname,
 tblAssetCustom.Manufacturer,
 tblAssetCustom.Model,
 tblAssets.AssetName,
 tblAssets.IPAddress,
 tblAssets.Domain,
 tsysAssetTypes.AssetTypename,
 tblAssetCustom.PurchaseDate As [Purchase Date],
 tblAssetCustom.Warrantydate As [Warranty Expiration],
 tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssetCustom
 Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
 Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
 Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblAssetCustom.Manufacturer = 'Dell Inc.' And tblAssets.AssetName Like '%-%'
And tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.Warrantydate <
 GetDate() And tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc

[treichert]

Here's the query - I can put %-% in the assetname column after running the query but when I put %-% into the criteria box, then it doesn't give me anything at all. I've tried changing it to Like, but that doesn't produce any results either.

Select Top 1000000 tblAssetCustom.AssetID,
tblADusers.Description,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblAssetCustom.Manufacturer = 'Dell Inc.' And tblAssets.AssetName = '%-%'
And tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.Warrantydate <
GetDate() And tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc

[Daniel_B]

Could you post the SQL query of your report after you added the criterion? If I enter %-% into a filter criteria field, it will automatically change to

Like '%-%'

and the report works for me.