SQL Select Failed Syntax Error 3706: expected something between '(' and the string 'i'. What am I missing?

I replaced as suggested, but now I get

SELECT Failed. 3706:  Syntax error: expected something between a string or a Unicode character literal and ','. 

FROM
  (SELECT DISTINCT knum, SUBSTRING(knum+'i',1, CHARINDEX('i', knum+'i', 1)-1) as confirmation_nbr
  FROM translog
  WHERE ((Bgn01 = '581' AND msg LIKE '%Status%C%') OR
  (Bgn01 = '628' AND DATALENGTH(knum) > 0))
  AND CONVERT(date, trans_date, 101) = :dbrDate
  )

Any suggestions?

I don't recognize the use of the colon in either of these two places.

CONVERT(date, trans_date, 101) = :dbrDatelo.partnercode = :partnerCodeCan you sort me out, please? thanks.

Below is the full query:SELECT DISTINCT creser.knum AS res_no , creser.confirmation AS local_res_no , cra001.knum AS rental_no , cra001.knum AS raw_rental_no , invoicedata.invoicenum AS invoice_nbr FROM (SELECT DISTINCT knum, CASE WHEN CHARINDEX('i', knum, 1) = 0 THEN knum ELSE SUBSTRING(knum, 1, CHARINDEX('i', knum, 1)-1) END AS confirmation_nbr FROM translog WHERE ((Bgn01 = '581' AND msg LIKE '%Status%C%') OR (Bgn01 = '628' AND DATALENGTH(knum) > 0)) AND CONVERT(date, trans_date, 101) = :dbrDate ) A INNER JOIN cpay ON cpay.knum = A.knum INNER JOIN cra001 ON cra001.knum = cpay.knum INNER JOIN setup lo ON cra001.loc_out = lo.location LEFT OUTER JOIN creser ON creser.confirmation = A.confirmation_nbr LEFT OUTER JOIN invoicedetails ON invoicedetails.cpayid = cpay.id LEFT OUTER JOIN invoicepaymentdetails invpay ON invpay.cpayid = cpay.id OUTER APPLY (select invoicedata.invoicenum FROM invoicedata WHERE invoicedata.id = invoicedetails.invoicedataid UNION SELECT invoicedata.invoicenum FROM invoicedata WHERE invoicedata.id = invpay.invoicedataid AND cpay.co = invoicedata.conum) AS invoicedata WHERE cra001.post_flag = 1 AND cra001.type = 'C' AND invoicedata.invoicenum IS NOT null AND cpay.pay_charge IN ('C','P') AND cpay.type NOT IN ('3') AND lo.partnercode = :partnerCode;I've tried removing the below:FROM (SELECT DISTINCT knum, CASE WHEN CHARINDEX('i', knum, 1) = 0 THEN knum ELSE SUBSTRING(knum, 1, CHARINDEX('i', knum, 1)-1) END AS confirmation_nbr

I then get this error: SELECT Failed. 3706:  Syntax error: Data Type "knum" does not match a Defined Type name.  Thanks for your help.

This can't be your entire SQL statement.  Can you provide more?

Also, I typically try to create a statement with just a small piece of the expression. When that works, I add another small piece and so on until it runs successfully. You have a number of expressions I would strip out and test.

This is a SQL server question, not MS Access. You might find help here but it would make more sense to post in a SQL Server Programming forum. 

I don't think this is an issue with CHARINDEX. I just tested this successfully.

WITH

Translog as

(SELECT 'This is a test' As knum

 UNION SELECT 'the dogs are red' 

 UNION SELECT 'oranges and apples'

 UNION SELECT 'Ice Cream Cones')

SELECT DISTINCT knum, 

 CASE WHEN CHARINDEX('i', knum, 1) = 0 THEN knum ELSE SUBSTRING(knum, 1, CHARINDEX('i', knum, 1)-1) END AS confirmation_nbr

  FROM translog

I expect your CASE WHEN could be replaced by:

 SUBSTRING(knum+'i',1, CHARINDEX('i', knum+'i', 1)-1) as confirmation_nbr