Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: VBA Type Mismatch error

    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.....

  2. #2
    Join Date
    Jan 2004
    The Netherlands
    "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....


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts