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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
Jim HornMicrosoft SQL Server Data DudeCommented:
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.
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'))))"
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim HornMicrosoft SQL Server Data DudeCommented:
Also copy-paste the entire SQL of a query that failed into this question.
f19lAuthor Commented:
However, as already stated when I combine together then I get the type mismatch error.
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
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

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

Unfortunately I do not have SQL Enterprise Manager.
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

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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post here the SqlStmt value before it being executed?
f19lAuthor Commented:
Randy, your code works. Thanks a lot.
Vitor MontalvãoMSSQL Senior EngineerCommented:
So it was a parenthesis issue?
f19lAuthor Commented:
Something to do with that I expect.
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.
f19lAuthor Commented:
Thanks. That clears it up.
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.