I'm having trouble creating a query table from VBA
I am pulling data from an SQL database (ODBC). The field titles come in but no data. I receive no errors and the data load is well within the limits of a single sheet for lines

If I "edit" the query in MS Query, make no changes and exit Query, the data shows up fine


With ActiveSheet.QueryTables.Add( _
    Connection:=sConn, _
        Destination:=ActiveSheet.Range("A3"), _
        Sql:=sSql)

    .RefreshStyle = xlOverwriteCells
    .Refresh

End With


For organization I setup a long string to handle the SELECT statement

    sSql = "SELECT inv_trx.trx_date, inv_trx.trx_qty,"
    sSql = sSql & " inv_trx.unit_price, inv_trx.unit_cost,"
    sSql = sSql & " inv_trx.ord_qty, inv_trx.comm_cost,"
    sSql = sSql & " inv_trx.addt_cost, inv_trx.cust_no,"
    sSql = sSql & " inv_trx.order_no, inv_trx.lin_no,"
    sSql = sSql & " inv_trx.doc_no, inv_trx.trx_type,"
    sSql = sSql & " ord_hedr.ord_class, ord_hedr.ord_type,"
    sSql = sSql & " item.prod_cat, customer.reg_code, customer.exclude_sa"
    sSql = sSql & " FROM customer, inv_trx, item, ord_hedr"
    sSql = sSql & " WHERE customer.cust_no = inv_trx.cust_no "
    sSql = sSql & " AND customer.cust_no = ord_hedr.cust_no "
    sSql = sSql & " AND inv_trx.order_no = ord_hedr.order_no "
    sSql = sSql & " AND inv_trx.item_no = item.item_no "
    sSql = sSql & " AND inv_trx.manu_no = item.manu_no "
    sSql = sSql & " AND ((customer.exclude_sa=0) "
    sSql = sSql & " AND (inv_trx.trx_date>="
    sSql = sSql & STARTDATE
    sSql = sSql & " AND inv_trx.trx_date<"
    sSql = sSql & ENDDATE
    sSql = sSql & ") "
    sSql = sSql & " AND (ord_hedr.ord_class<>'M'"
    sSql = sSql & " And ord_hedr.ord_class<>'P') "
    sSql = sSql & " AND (ord_hedr.ord_type<>'S')) "
    sSql = sSql & " ORDER BY inv_trx.trx_date"

From what I found on the web nobody else is having this issue so I must be missing something simple

Help!

Dave

are you sure your date format is correct in STARTDATE and ENDDATE? try removing these conditions. If you recieve data, then I would guess this was the problem..

Thanks! that was it. Although I formated the variable as a Date value I forgot that it treats it as a number and that is what is passed to the sql string. I guess I'll have to build the date/time value independantly (simple to do) and pass it as a text string.

A second pair of eyes always catches the simple stuff so easy

Thanks again!