In our LanSweeper installation I have a custom asset field (Custom04) with a type of Currency. This field is where I track what we originally paid for an asset. The goal of the report is to show assets purchased in 2017 in which the Custom04 field is >= 1500.
Currently this is what my query looks like:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.PurchaseDate,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Custom4 As [Original Purchase Price],
tblAssets.Description
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.PurchaseDate >= Cast('01-01-2017' As DATETIME) And
tblAssetCustom.PurchaseDate <= Cast('12-31-2017' As DATETIME) And
tblAssetCustom.Custom4 >= '1500' And tblAssetCustom.State = 1
I've tried a couple variations of the tblAssetCustom.Custom4 >= '1500' without success. When I run the report as is it will just show all assets with a PurchaseDate in 2017. If I take the quotes off around the number I get the error of: Error: Data conversion failed. [ OLE DB status value (if known) = 2 ]
I will say that I really don't have any SQL query experience outside of hacking together reports like this, but I'd like to understand what I'm doing wrong with this report. Right now my current theory and thought process is:
-When I looked up the Custom04 field in the database documentation I see that the type is nvarchar, which as I understand is essentially a string
-Even though I have the Type set as Currency in the Asset Custom Fields section, it is still being stored as a string
-Since it is a string it can't evaluate the >= criteria, because it would be on the same level of asking "Is the word 'Red' >= the word '1500'?", and when the quotes are removed I get the error because that is trying to evaluate if a string is greater than a number.
Am I on the right track of understanding what is going on with my report? If so is there any sort of way I could work around this with how I structure the criteria? Any help would be greatly appreciated!