I am relatively new to this, so maybe I am overlooking something
simple. I am trying to run some queries via VB and can get most of
them to work. However, when I use a Case when statment I get a type
missmatch error. After copying the debug.print to my query analyzer
or directly into Microsoft query, I don't have a problem. It's only
when I run if from VB. Here is my code, if anyone can please help:

stsql = "Set NoCount on " & _
"Select " & _
"CM.Certmainseqno , RefDate = CM.activitydate " & _
"Into #POL from DayOld..Commtrans as CM " & _
"where CM.activitydate = '06/01/04' and CM.commpayee =
'33816230' " & _
"Set NoCount Off " & _
"Select Test = case when RefDate = '06/03/04' then RefDate
else GetDate() end " & _
"From #Pol "
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Warehouse;Description=DataWarehouse
Summary Database;DATABASE=Warehouse;UseProcForPrepare=0" _
, Destination:=Range("A1"))
.CommandText = Array( _
stsql)
.Name = "GetPols"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Everything works fine if I do not include the case statement. All
records are returned if if have the last piece as select * from #Pol.

Any help would be appreciated.

(Henrik) wrote ...

I am trying to run some queries via VB and can get most of
them to work. However, when I use a Case when statment I get a type
missmatch error. After copying the debug.print to my query analyzer
or directly into Microsoft query, I don't have a problem. It's only
when I run if from VB.

Have you tried stopping execution after the stsql = <<text line,
capturing (e.g. in the Immediate Window) the value of stsql and
pasting this into the MS Query SQL window? Sometimes things go awry
when converting from plain text to VBA code so might be worth checking
again.

Jamie.

--

(Jamie Collins) wrote in message om...
(Henrik) wrote ...

I am trying to run some queries via VB and can get most of
them to work. However, when I use a Case when statment I get a type
missmatch error. After copying the debug.print to my query analyzer
or directly into Microsoft query, I don't have a problem. It's only
when I run if from VB.

Have you tried stopping execution after the stsql = <<text line,
capturing (e.g. in the Immediate Window) the value of stsql and
pasting this into the MS Query SQL window? Sometimes things go awry
when converting from plain text to VBA code so might be worth checking
again.

Jamie.

--

Thanks for the reply Jamie, and yes, pasting it into MS Query SQL
window works fine. However, after looking into the problem a little
more, it appears that it is not related to the Case when statement,
rather it is more fundamental. When I select * from the temporary
table, everything is returned; if I only select one field, it is
returned; however, as soon as I request more than one field by name, I
get the same error (13-Type Mismatch). This has created good news.
After looking into it, I realized that it has to do with the Array
portion of the .CommandText. There is a character limit that I was
not thinking of. After chunking the SQL and removing the Array from
the .commandtext everythin works fine. Like always, when you think
something is really screwed up, it's usually something simple.