Why does my Vsual Basic SQL fail

Hello,

I have a very basic SQL code in my excel spreadsheet VB module that filters data from a table based upon the following condition:

Condition = "(((BR='01') AND (TRAD In ('LTSY','CCC','SPM','NJH','TRYS'))) OR ((BR='05') AND (TRAD In ('LRF5','LRS5','LSFO'))) OR ((BR='02') AND (TRAD In ('2SPO','2FOR'))))"

When I try to run my code I keep getting type mismatch but when I choose just any of the two above conditions and run the code then it works. Has anyone come across a problem like this before or know how to fix this?

Thanks.
f19lAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
Can you post what does work
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What is the column data types for BR, TSAD, etc. as you are passing characters ' ', so if any of them are numeric and couldn't convert by implicit conversion then the query will fail.
0
f19lAuthor Commented:
Any of these conditions will work.

Condition = "(((BR='01') AND (TRAD In ('LTSY','CCC','SPM','NJH','TRYS'))) OR ((BR='05') AND (TRAD In ('LRF5','LRS5','LSFO'))))"

Condition = "(((BR='01') AND (TRAD In ('LTSY','CCC','SPM','NJH','TRYS'))) OR ((BR='02') AND (TRAD In ('2SPO','2FOR'))))"

Condition = "(((BR='05') AND (TRAD In ('LRF5','LRS5','LSFO'))) OR ((BR='02') AND (TRAD In ('2SPO','2FOR'))))"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also copy-paste the entire SQL of a query that failed into this question.
0
f19lAuthor Commented:
However, as already stated when I combine together then I get the type mismatch error.
0
f19lAuthor Commented:
db = "OPICS_CUSTOM.POSTBATCH_CPPR POSTBATCH_CPPR"
    Fields = "BR, TRAD, VDATE, CCY1, CCY2,  CCY1AMT, CCY2AMT, CCY1MKTAMT, CCY2MKTAMT"
    Condition = "(((BR='01') AND (TRAD In ('LTSY','CCC','SPM','NJH','TRYS'))) OR ((BR='05') AND (TRAD In ('LRF5','LRS5','LSFO'))) OR ((BR='02') AND (TRAD In ('2SPO','2FOR'))))"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array("ODBC;DSN=Opics ProdR Oracle;UID=f19l;pwd=Hello;Trusted_Connection=Yes;DBQ=WRPRD01Z0122;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;" _
        ), Array("LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F")), Destination:=Range(queryadd)).QueryTable
        .CommandText = Array("SELECT " & Fields & " FROM " & db & " WHERE " & Condition & "")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
0
Randy PooleCommented:
Try to simply your condition as:
Condition = "(BR='01' AND TRAD In ('LTSY','CCC','SPM','NJH','TRYS')) OR (BR='05' AND TRAD In ('LRF5','LRS5','LSFO')) OR (BR='02' AND TRAD In ('2SPO','2FOR'))"

Open in new window

0
Randy PooleCommented:
Also try to execute the resultant SQL in SQL Enterprise Manager
0
f19lAuthor Commented:
I tried the simplified condition but it still fails.

Unfortunately I do not have SQL Enterprise Manager.
0
Randy PooleCommented:
Give this a try:
 Dim SqlStmnt As String, db As String, Condition As String
 
 db = "OPICS_CUSTOM.POSTBATCH_CPPR POSTBATCH_CPPR"
 Fields = "BR, TRAD, VDATE, CCY1, CCY2,  CCY1AMT, CCY2AMT, CCY1MKTAMT, CCY2MKTAMT"
 Condition = "(BR='01' AND TRAD In ('LTSY','CCC','SPM','NJH','TRYS')) OR (BR='05' AND TRAD In ('LRF5','LRS5','LSFO')) OR (BR='02' AND TRAD In ('2SPO','2FOR'))"
 SqlStmnt="SELECT " & Fields & " FROM " & db & " WHERE " & Condition
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array("ODBC;DSN=Opics ProdR Oracle;UID=f19l;pwd=Hello;Trusted_Connection=Yes;DBQ=WRPRD01Z0122;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;" _
      ), Array("LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F")), Destination:=Range(queryadd)).QueryTable
      .CommandType = xlCmdSql
      .CommandText = SqlStmt
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = False
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .Refresh BackgroundQuery:=False
  End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Randy PooleCommented:
BTW you can download and install SQL Server Express 2014 to install Enterprise Manager.  There is no cost.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post here the SqlStmt value before it being executed?
0
f19lAuthor Commented:
Randy, your code works. Thanks a lot.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So it was a parenthesis issue?
0
f19lAuthor Commented:
Something to do with that I expect.
0
Randy PooleCommented:
No, modified it so it uses a string instead of an object for the commandtext.  You will notice no more Array() and also added commandtype attribute to specify this.
1
f19lAuthor Commented:
Thanks. That clears it up.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.