Type Mismatch when Running SQL Query

[safcftm117]

I have a function which is querying an external database. It is called by another program which creates the where clause which is then passed dynamically into the SQL query.

The WHERE clause is passed in as a string parameter and the function works perfectly until the string exceeds a certain length (around 150 characters). When the WHERE clause exceeds this length the program fails with a Type Mismatch error.

My understanding is that a string can contain over 1bn characters so I'm unsure as to why this error is occurring.

My function is:
Sub Query_sub(wc)
'
'
'

'
Dim WhereClause
WhereClause = wc

ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=e-Work Test;Description=Metastorm Data Source;UID=ework;APP=Microsoft Office 2003;;DATABASE=e-work;Trusted_Con" _
), Array("nection=Yes")), Destination:=Range("A1"))
Range("A1") = WhereClause
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM ""e-work"".dbo.Generic_Matrix Generic_Matrix" & Chr(13) & "" & Chr(10) & "" & WhereClause & "" & Chr(13) & "" & Chr(10) & "ORDER BY Sequence" _
)
.Name = "Query from e-Work Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

An example of the where clause is:
WHERE (BoM_Level IN ('0')) AND (Grp IN ('PHANTM')) AND (Issue_Loc IN ('3005')) AND (CC IN ('6382')) AND (Supplier IN ('PRD000051')) AND (Material IN ('080M50')) AND (Res_Code IN ('12A4','12A4')) AND (Unit_Code IN ('C0310','C0310')) AND (Model IN ('PC138US-8 WE SUB','PC160LC-8','PC190NLC-8','PC210LCD-8','PC210LCD-8')) AND (Commodity IN ('8539293000')) AND (Lev1_BoM IN ('X950138170')) AND (Imm_Lev_BoM IN ('0102481020')) AND (Cur IN ('JPY','JPY')) AND (Ctry IN ('IDN'))

 

[strongm]

Two questions:

1) Exactly which line does the mismatch occur on?

2) Why you are using the Array function?

 

[safcftm117]

The original program was passed to me by a user who had written (or recorded I'm guessing) a hardcoded version of the query. I hadn't tried removing the Array function but now you mention it removing it works. Stupid me

Thanks

 

[Andrzejek]

Is it possible for one (or several) of your passed parameters to have a single quote in them? Looks like there are a lot of strings passed into your IN parts of the query, so something like this would mess it up:

WHERE (BoM_Level IN ('0')) 
AND (Grp IN ('PHANTM')) 
AND (Issue_Loc IN ('3005')) 
AND (CC IN ('6382')) 
AND (Supplier IN ('PRD000051')) 
AND (Material IN ('080M[red][b]'[/b][/red]50')) 
AND (Res_Code IN ('12A4','12A4')) 
AND (Unit_Code IN ('C0310','C0310')) 
AND (Model IN ('PC138US-8 WE SU[red][b]'[/b][/red]B','PC160LC-8','PC190NLC-8','PC210LCD-8','PC210[red][b]'[/b][/red]LCD-8')) 
AND (Commodity IN ('8539293000')) 
AND (Lev1_BoM IN ('X950138170')) 
AND (Imm_Lev_BoM IN ('0102481020')) 
AND (Cur IN ('JPY','JP[red][b]'[/b][/red]Y')) 
AND (Ctry IN ('IDN'))

I added some red single quotes to your query, not easy to see them.

Have fun.

---- Andy

 

[strongm]

I thin we've fixed it already, Andy. Unless I am misunderstanding safcftm117's last post

 

[Andrzejek]

You are right strongm, he has it fixed.
Dissregard my comment.

Have fun.

---- Andy