Hello All,

I am running a VBA Module in excel to grab data from a SQL Server. I am running into a Type Mismatch error in my where clause. If I try and have more that 2 or 3 where clauses it dies. The problem is I have up to 60 Where Clauses to pass. I am searching for a staus and base clause for 60 bases. Here is my Code

With Sheet10.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Remedy;ARServer=" & server & ";ARServerPort=XXXX;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) & "WHERE (HPD_HelpDesk.Status<='Resolved') AND (HPD_HelpDesk.Base= 'ABNY') OR (HPD_HelpDesk.Status<='Resolved') AND (HPD_HelpDesk.Base= 'ADLP')" & 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


It works like this but if I put any more Status and base where clauses it will die with a type mismatch. Please help.....

"WHERE (HPD_HelpDesk.Status<='Resolved') AND (HPD_HelpDesk.Base= 'ABNY') OR (HPD_HelpDesk.Status<='Resolved') AND (HPD_HelpDesk.Base= 'ADLP')"

For 1 thing this query may end up giving you unexpected results....
1) <='Resolved', dont do that. You shouldnt use calculative (<= >= etc) expresions on Strings this will have (for you) unexpected results. Its better to use <> or !=
2) the and ... or ... and construction is 'free' to be interperted by your AR db. its better to tell it how to interpet it, by doing (.... and ....) or (... and ...)


Now for maybe some tips ...
how about using the in operator
WHERE (HPD_HelpDesk.Status<='Resolved') AND (HPD_HelpDesk.Base in ('ABNY','ADLP')"

ALL base = part must be enclosed in '', maybe you should post a bit of sql that does crash....

Regards