Variable (inputbox) to be assigned in select CommandText -VBA

Question

Hi 

i am refining a recored macro , that pulls data from FoxPro to excel.code is correct i just want to add inputbox so as to get desire data i added highlighted command ,but if i write  **WHERE (HS_FILE.TR_TYPE='ttype')**variable****with single comma no data is extracted & if i write  **WHERE (HS_FILE.TR_TYPE=ttype)**without comma,****it shows "General ODBC error".

Thanks

Sub db_fox()

   Dim zCode As String

Dim tType As String

---

zCode = InputBox("Enter Zone Code :")

tType = InputBox("Enter Transaction Type :")

   With ActiveSheet.ListObjects.Add(

        "ODBC;CollatingSequence=ASCII;

        ), Array( _

        "L=dBase 5.0;MaxBufferSize=2048;

        )), Destination:=Range("$A$1")).

        .CommandText = Array( _

        "SELECT HS_FILE.TR_TYPE, HS_FILE.RTE_CODE, HS_FILE.ZNE_CODE, HS_FILE.PRD_CODE, HS_FILE.TR_QTY" & Chr(13) & "" & Chr(10) & "FROM HS_FILE HS_FILE" & Chr(13) & "" & Chr(10) & "WHERE (HS_FILE.TR_TYPE=ttype) AND (HS_FILE.ZNE_CODE=zcode)" _

        )

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .PreserveColumnInfo = True

        .ListObject.DisplayName = "Table_Query_from_dpg"

        .Refresh BackgroundQuery:=False

    End With

End Sub

Answer 1

The problem is that the query you are creating contains the literal string tttype which means nothing to the database.  It is a variable local to your VBA procedure and you want the value of that variable to be part of the string.  You do that by concatenating the variable with the rest of the string, and surrounding it with ' ' for the sake of the SQL syntax so:

"WHERE (HS_FILE.TR_TYPE='" & ttype & "') AND (HS_FILE.ZNE_CODE='" & zcode & "')"

Answer 2

Perfect ! thanks again ...

Answer 3

Thanks sir, i 'll try it