ODBC SQL Query give type mismatch in where clause
 

Hello Alll,
I am trying to pull data from a SQL server app called Remedy. I
has a ODBC driver that I am using. I am trying to edit an existing se
of code to manipulate it to my conditions. The situation I have is th
existing code will only work for 1 "Base" code in the where clause an
1 Status. I support 60 Bases. I need to configure a Where clause tha
supports 60 Bases. The problem I run in to is every time I go over
bases / status where clauses I run into a type mismatch.

Here is my code can anyone help..

Public Sub Querry()

' Querry of the Remedy Server For Tickets

With Sheet10.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Remedy;ARServer=" & server
";ARServerPort=5280;UID=" & UserName & ";PWD=" & password
";ARAuthentication=;ARDiaryDescend=1;ARUseUn" _
), Array("derscores=1;ARNameReplace=1;SERVER=NotTheSe rver"))
Destination:=Sheet10.Range("A2"))
.CommandText = Array( _
"SELECT HPD_HelpDesk.Create_Time, HPD_HelpDesk.Case_ID_
HPD_HelpDesk.Status, HPD_HelpDesk.Escalated_, HPD_HelpDesk.Base
HPD_HelpDesk.Assigned_To_Group_, HPD_HelpDesk.Category
HPD_HelpDesk.Type, HPD_HelpDesk.Item," _
, _
" HPD_HelpDesk.Machine_Name_, HPD_HelpDesk.Description
HPD_HelpDesk.Work_Log, HPD_HelpDesk.Requester_Name_
HPD_HelpDesk.Phone_Number, HPD_HelpDesk.Building, HPD_HelpDesk.Floor
HPD_HelpDesk.Room_Cube, HPD_HelpDesk.Network_Jack" & Chr(13) & ""
Chr(10) & "FROM HP" _
, _
"D_HelpDesk HPD_HelpDesk" & Chr(13) & "" & Chr(10) & "WHER
(HPD_HelpDesk.Status<='Resolved') AND (HPD_HelpDesk.Base= 'ABNY')"
Chr(13) & "" & Chr(10) & "ORDER BY HPD_HelpDesk.Case_ID_")
.Name = "Query from AR System ODBC Data Source"
.FieldNames = False
.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

' Sheet7.PivotTables("Count of Case ID").RefreshTable
' Sheet9.PivotTables("MACs").RefreshTable

End Sub

The example only shows 1 Status / Base in the where clause and a
previously stated I need to get the results for 60 bases on th
existing status...

--
Message posted from http://www.ExcelForum.com

ODBC SQL Query give type mismatch in where clause
 

hi,

<='Resolved'

cannot work, i'd guess. I would suggest that you first test your sql-string
as plain text without any concatenating, variables etc.

regards

arno

"WHERE
(HPD_HelpDesk.Status<='Resolved') AND (HPD_HelpDesk.Base= 'ABNY')" &
Chr(13) & "" & Chr(10)