Run-time error 13 type mismatch

[Naug]

I am making a QueryTable in Excel and ran into this error.
I set query command text so

.CommandText = Array("SELECT TOP 50 regn1,name, count (*) AS Counter_agents, sum(obk_sum) AS OBK, sum(obk_sum)*count(*) FROM (SELECT regn1, regn2, sum(obk) as obk_sum FROM kp0407 WHERE (BAL =31302 OR BAL=31303 or bal=31301) GROUP BY regn1, regn2 having sum(obk)>0), banc GROUP BY regn1,name;")

and get Run-time error 13 type mismatch. However when I run same query directly in access it doesnt cause any problems. What am I doing wrong and what is the remedy?

 

[PHV]

.CommandText = "SELECT TOP 50 regn1,banc.name,Count(*) AS Counter_agents,Sum(obk_sum) AS OBK,Sum(obk_sum)*Count(*) AS X FROM (" _
& "SELECT regn1,regn2,Sum(obk) AS obk_sum FROM kp0407 WHERE BAL In (31302,31303,31301) GROUP BY regn1,regn2 HAVING Sum(obk)>0" _
& ") AS A, banc GROUP BY regn1,banc.name"

BTW is kp0407.BAL defined as numeric ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

The error comes from trying to assign the CommandText to an ARRAY but then not making it an actual array by only having 1 element.....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question