asked on

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.

Randy Poole

Can you post what does work

Jim Horn

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.

f19l

ASKER

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'))))"

Jim Horn

Also copy-paste the entire SQL of a query that failed into this question.

f19l

ASKER

However, as already stated when I combine together then I get the type mismatch error.

f19l

ASKER

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 Poole

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 Poole

Also try to execute the resultant SQL in SQL Enterprise Manager

f19l

ASKER

I tried the simplified condition but it still fails.

Unfortunately I do not have SQL Enterprise Manager.

ASKER CERTIFIED SOLUTION

Randy Poole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Randy Poole

BTW you can download and install SQL Server Express 2014 to install Enterprise Manager. There is no cost.

Vitor Montalvão

Can you post here the SqlStmt value before it being executed?

f19l

ASKER

Randy, your code works. Thanks a lot.

Vitor Montalvão

So it was a parenthesis issue?

f19l

ASKER

Something to do with that I expect.

Randy Poole

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.

f19l

ASKER

Thanks. That clears it up.