how to do a contains statement in sql?

[Dark0Prince]

Select 
MLB8.PROG_REPORT_ACCT_NUM_AND_PRACCT, 
DCGI.Date_Listed, 
DB.INITIAL_BALANCE, 
DS.Last_Payment, 
LV.Amt_Paid_On_Acct, 
DB.Principal_Balance, 
MLB7.INVENT_INVEN2
From
Clnt_General CG
Inner Join
Dbtr_Clnt_Generl_Inf DCGI on CG.Client_Num1=DCGI.Client
Inner Join
Dbtr_Status DS on DCGI.Account_Num1=DS.Account_Num1
Inner Join
Dbtr_Balances DB on DCGI.Account_Num1=DB.Account_Num1
Inner Join 
Debtor_Master_Logic_Blocks7 MLB7 on DCGI.Account_Num1=MLB7.Account_Num1
Inner Join 
Debtor_Master_Logic_Blocks8 MLB8 on DCGI.Account_Num1=MLB8.Account_Num1
Inner Join 
Letter_Variables LV on DCGI.Account_Num1=LV.Account_Num1
Where
CG.Client_Num1=?
And 
DCGI.Date_Listed>?

This is my current code and I'm trying to add a where filter that says: when 4 5 6 7 10 15 20 21 25 35 36 37 40 45 46 57 58 65 75 86 87 91 92 99 numbers are not contained in DS.Cancel_Reason

 

[Joe4]

Try:

Where Ds.Cancel_Reason Not In (4,5,6,7,10,15,20,21,25,35,36,37,40,45,46,57,58,65,75,86,87,91,92,99)

 

[Dark0Prince]

Select 
MLB8.PROG_REPORT_ACCT_NUM_AND_PRACCT, 
DCGI.Date_Listed, 
DB.INITIAL_BALANCE, 
DS.Cancel_Reason,
DS.Last_Payment, 
LV.Amt_Paid_On_Acct, 
DB.Principal_Balance, 
MLB7.INVENT_INVEN2
From
Clnt_General CG
Inner Join
Dbtr_Clnt_Generl_Inf DCGI on CG.Client_Num1=DCGI.Client
Inner Join
Dbtr_Status DS on DCGI.Account_Num1=DS.Account_Num1
Inner Join
Dbtr_Balances DB on DCGI.Account_Num1=DB.Account_Num1
Inner Join 
Debtor_Master_Logic_Blocks7 MLB7 on DCGI.Account_Num1=MLB7.Account_Num1
Inner Join 
Debtor_Master_Logic_Blocks8 MLB8 on DCGI.Account_Num1=MLB8.Account_Num1
Inner Join 
Letter_Variables LV on DCGI.Account_Num1=LV.Account_Num1
Where
CG.Client_Num1=?
And 
DCGI.Date_Listed>?
And
Where DS.Cancel_Reason Not In (4,5,6,7,10,15,20,21,25,35,36,37,40,45,46,57,58,65,75,86,87,91,92,99)

I added it but I get this long error code "..A term expected, beginning with one of the following: identifier, constant, aggregate, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %SQLUPPER, ..."

 

[Joe4]

You add it to your existing WHERE clause (you cannot have two WHERE clauses like that).
(Sorry if I wasn't clear on that, I shouldn't have assumed that you are an experienced SQL code writer).

So your total WHERE clause would look like:

Where
CG.Client_Num1=?
And 
DCGI.Date_Listed>?
And
DS.Cancel_Reason Not In (4,5,6,7,10,15,20,21,25,35,36,37,40,45,46,57,58,65,75,86,87,91,92,99)

(except I hope you really have something other "?" at the end of the first two conditions, because those are not valid SQL statements).

 

[Dark0Prince]

LOL i knew not to do that but didn't notice it there. Also the ? work as sql statements because they prompt for the parameters when you refresh or you can set them to associate with cells in excel.

 

[Joe4]

Also the ? work as sql statements because they prompt for the parameters when you refresh or you can set them to associate with cells in excel.

I did not know that, but I have only used SQL in native SQL or Access. I have never used it with Excel.
I learned something new today!