It does not return any result but I can see 153750 in amount field.
Since it is a search textbox let user input either string or decimal, it can return query result if I input string. If I input number, it does not return.

but the user can input nothing, case when '%153750%'='XX' then 1
or
input string, when t.amount = case(isnumeric(Substring('%153750%', 2, LEN('%153750%')-2)))
or
input decimal / number , when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))

I think my question was not clear enough. There is a aspx page which has a textbox let user input a string, e.g. kat, 153750, 8823.67, then it will search the alltransaction table. For default, textbox is empty, it will list out all records.

Case 1: Empty textbox

select [id]=t.id, [alltranid], [transactiontypeid], [type]=r.type, [soldstockid], [clientid]=t.clientid, [nickname]=c.nickname,
[currency]=t.currency, [ucurrency]=t.currency, [rate], [amount]=CONVERT(VARCHAR,CONVERT(MONEY,t.amount),1), [countervalue], [clientreference],
[handlingincome], [handlingexpense], [trandatetime], [status]
from alltransaction t inner join client c on t.clientid = c.id inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id inner join account a on t.accountid = a.id inner join account n on t.lastaccountid = n.id
where alltranid=0 and status = 0 and soldstockid = 0 and (
('%%'='XX' or t.clientreference like N'%%') or ('%%'='XX' or r.type like N'%%') or
('%%'='XX' or c.nickname like N'%%') or ('%%'='XX' or t.currency like N'%%') or
(case when '%%'='XX' then 1 when t.amount = case(isnumeric (Substring('%%', 2, LEN('%%')-2))) when 1 then convert(decimal, Substring('%%', 2, LEN('%%')-2)) else 0 end THEN 1 else 0 END = 1) or
('%%'='XX' or t.remarks like N'%%') or ('%%'='XX' or t.remarks1 like N'%%') or ('%%'='XX' or t.remarks2 like N'%%') or
('%%'='XX' or t.remarks3 like N'%%') or ('%%'='XX' or t.remarks4 like N'%%') or ('%%'='XX' or t.remarks5 like N'%%') or
('%%'='XX' or t.remarks6 like N'%%') or ('%%'='XX' or t.remarks7 like N'%%')
) order by [id] asc

Case 2: kat in textbox

select [rowselector]=0, [id]=t.id, [alltranid], [transactiontypeid], [type]=r.type, [soldstockid], [clientid]=t.clientid, [nickname]=c.nickname,
[currency]=t.currency, [ucurrency]=t.currency, [rate], [amount]=CONVERT(VARCHAR,CONVERT(MONEY,t.amount),1), [countervalue], [clientreference],
[handlingincome], [handlingexpense], [trandatetime], [status]
from alltransaction t inner join client c on t.clientid = c.id inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id inner join account a on t.accountid = a.id inner join account n on t.lastaccountid = n.id
where alltranid=0 and status = 0 and soldstockid = 0 and (
('%kat%'='XX' or t.clientreference like N'%kat%') or ('%kat%'='XX' or r.type like N'%kat%') or
('%kat%'='XX' or c.nickname like N'%kat%') or ('%kat%'='XX' or t.currency like N'%kat%') or
(case when '%kat%'='XX' then 1 when t.amount = case(isnumeric (Substring('%kat%', 2, LEN('%kat%')-2))) when 1 then convert(decimal, Substring('%kat%', 2, LEN('%kat%')-2)) else 0 end THEN 1 else 0 END = 1) or
('%kat%'='XX' or t.remarks like N'%kat%') or ('%kat%'='XX' or t.remarks1 like N'%kat%') or
('%kat%'='XX' or t.remarks2 like N'%kat%') or ('%kat%'='XX' or t.remarks3 like N'%kat%') or
('%kat%'='XX' or t.remarks4 like N'%kat%') or ('%kat%'='XX' or t.remarks5 like N'%kat%') or
('%kat%'='XX' or t.remarks6 like N'%kat%') or ('%kat%'='XX' or t.remarks7 like N'%kat%')
) order by [id] asc

Case 3: 153750

select [id]=t.id, [alltranid], [transactiontypeid], [type]=r.type, [soldstockid], [clientid]=t.clientid, [nickname]=c.nickname,
[currency]=t.currency, [ucurrency]=t.currency, [rate], [amount]=CONVERT(VARCHAR,CONVERT(MONEY,t.amount),1), [countervalue], [clientreference],
[handlingincome], [handlingexpense], [trandatetime], [status]
from alltransaction t inner join client c on t.clientid = c.id inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id inner join account a on t.accountid = a.id inner join account n on t.lastaccountid = n.id
where alltranid=0 and status = 0 and soldstockid = 0 and (
('%153750%'='XX' or t.clientreference like N'%153750%') or ('%153750%'='XX' or r.type like N'%153750%') or
('%153750%'='XX' or c.nickname like N'%153750%') or ('%153750%'='XX' or t.currency like N'%153750%') or
(case when '%153750%'='XX' then 1 when t.amount = case(isnumeric (Substring('%153750%', 2, LEN('%153750%')-2))) when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else 0 end THEN 1 else 0 END = 1) or
('%153750%'='XX' or t.remarks like N'%153750%') or ('%153750%'='XX' or t.remarks1 like N'%153750%') or
('%153750%'='XX' or t.remarks2 like N'%153750%') or ('%153750%'='XX' or t.remarks3 like N'%153750%') or
('%153750%'='XX' or t.remarks4 like N'%153750%') or ('%153750%'='XX' or t.remarks5 like N'%153750%') or
('%153750%'='XX' or t.remarks6 like N'%153750%') or ('%153750%'='XX' or t.remarks7 like N'%153750%')
) order by [id] asc

Case 1 and 2 are ok, but Case 3 cannot list the record.

0

Featured Post

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple. The final solution for this task involved creating a custom text parser and then reading…

Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.