Error 2147217900: Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.
This is the error message that I got when trying the advanced search for 'Contains' on an SQL Server money field. I was able to fix it for the regular search by casting the data as varchar in the SQL statement. Following was my attempt to manually change advanced search:
' advanced search
if Request.Form("action")="AdvancedSearch" then
sWhere=""
For i = 1 To Request.Form("SearchFor").Count
strSearchFor=Trim(Request.Form("SearchFor")(i))
if strSearchFor<>"" then
if sWhere="" then sWhere=" where 1=1"
strSearchOption=Trim(Request.Form("SearchOption")(i))
if Request.Form( ("FieldName")(i) ) = "Original Cost" then
sWhere = sWhere & " and " & " (cast( " & AddWrappers(Request.Form("FieldName")(i)) & " as varchar(25))) " & StrWhereAdv(Request.Form("FieldName")(i), strSearchFor, strSearchOption)
else
sWhere = sWhere & " and " & AddWrappers(Request.Form("FieldName")(i)) & StrWhereAdv(Request.Form("FieldName")(i), strSearchFor, strSearchOption)
end if
end if
next
strSQL = strSQL & sWhere
end if