"Where Custom1 > Number" Not Working

[Matt8]

Simple query, unexpected result. I am using the second Custom2 field to save Cost, since Lansweeper is about the only inventory system in the world without a field for it

Trying to see something over the cost of $500

SELECT a.AssetName, at.AssetTypename, ac.PurchaseDate, ac.Department, ac.Custom2 FROM tblAssets a
INNER JOIN tblAssetCustom ac ON a.AssetID = ac.AssetID
INNER JOIN tsysAssetTypes at ON a.Assettype = at.AssetType
WHERE ac.Custom2 > '499'

And the result set I get is wrong. I get items well under the amount.

The column appears to be formatted as nvarchar which would explain why this doesn't work.

If I were to change this column type to numeric, am I going to blow up Lansweeper?

[Susan_A · Accepted solution]

You will need to convert the values in your query, as suggested by RC62N as well. Custom fields always remain text fields within the Lansweeper database, regardless of what you set their display type to. Changing the field type directly in the database is not supported and may very well break certain parts of your installation.

[Matt8]

That would absolutely work, but will have to do that on any query that uses the custom fields. Seems like when you change the type of data in the column, Lansweeper should be formatting that column as such instead of leaving as nvarchar

[RCorbeil]

Rather than risk breaking your LANSweeper, could you not Convert() your custom value to an Int when evaluating it?