While it would be nice if your code would work (I'm in the same boat you
are), there is no way that it will.

The definition of a Pass Through query is one that is NOT manipulated by
Access or the Jet Engine at all.  Its sent directly to the server where
its run, and Access then reads the output and formats it.

Oracle is seeing the "Forms![frmSelection]![txtOrderFromDate] AND
Forms![frmSelection]![txtOrderToDate])" and doesn't know what you mean,
so it returns an error.  The best thing you can do is run a Stored
Procedure with variables for the two dates, and call the Store Proc from
a Pass Through query created in vba.  Either that or create the whole
query in vba.

I'm sorry to break the bad news, but these are the only ways to do it.
Or at least the only ways I know, and I've been searching for a couple
of weeks.  If you find a better way than this let me know.

Spencer

While it would be nice if your code would work (I'm in the same boat you
are), there is no way that it will.

The definition of a Pass Through query is one that is NOT manipulated by
Access or the Jet Engine at all.  Its sent directly to the server where
its run, and Access then reads the output and formats it.

Oracle is seeing the "Forms![frmSelection]![txtOrderFromDate] AND
Forms![frmSelection]![txtOrderToDate])" and doesn't know what you mean,
so it returns an error.  The best thing you can do is run a Stored
Procedure with variables for the two dates, and call the Store Proc from
a Pass Through query created in vba.  Either that or create the whole
query in vba.

I'm sorry to break the bad news, but these are the only ways to do it.
Or at least the only ways I know, and I've been searching for a couple
of weeks.  If you find a better way than this let me know.

Spencer

I have not seen the question, however, in code you can update the querydef with the value from txtOrderFromDate and txtOrderToDate, then run the pass-through query.

--
Regards,
Patrick McCarthy

  While it would be nice if your code would work (I'm in the same boat you are), there is no way that it will.
  The definition of a Pass Through query is one that is NOT manipulated by Access or the Jet Engine at all.  Its sent directly to the server where its run, and Access then reads the output and formats it.

  Oracle is seeing the "Forms![frmSelection]![txtOrderFromDate] AND Forms![frmSelection]![txtOrderToDate])" and doesn't know what you mean, so it returns an error.  The best thing you can do is run a Stored Procedure with variables for the two dates, and call the Store Proc from a Pass Through query created in vba.  Either that or create the whole query in vba.

  I'm sorry to break the bad news, but these are the only ways to do it.  Or at least the only ways I know, and I've been searching for a couple of weeks.  If you find a better way than this let me know.

  Spencer

    I have a SQL Pass-through query, which I want to take variables from a form.The following code does not work:SELECT ALL SIEBEL.S_CONTACT.LAST_NAME, SIEBEL.S_CONTACT.FST_NAME, SIEBEL.S_ORDER_ITEM.ORDER_ID,
    SIEBEL.S_ORDER.CONTACT_ID, SIEBEL.S_ORDER.STATUS_CD, SIEBEL.S_CONTACT_X.ATTRIB_42,
    SIEBEL.S_ORDER.X_DURA_DOCTOR, SIEBEL.S_ORDER.X_DURA_DOCTOR_PHONE,
    SIEBEL.S_ORDER.X_DURA_DISPENSING_PHARMACY, SIEBEL.S_ORDER.X_DURA_PHARMACY_ORDER_NUMBER,
    SIEBEL.S_ORDER.X_DURA_PRINT_FLAG, SIEBEL.S_PROD_INT.NAME,
    SIEBEL.S_ORDER_ITEM.QTY_REQ, SIEBEL.S_ORDER_ITEM.QTY_SHIPPED, SYSDATE-135 FromDate,
    SYSDATE-80 ToDate, SIEBEL.S_ORDER.ORDER_DT, SIEBEL.S_CONTACT.LAST_NAME||', '||SIEBEL.S_CONTACT.FST_NAME FullName,
    SIEBEL.S_CONTACT.HOME_PH_NUM,
    SIEBEL.S_ORDER.X_DURA_CC_DATE_SHIPPED, SIEBEL.S_CONTACT.INTEGRATION_ID
    FROM SIEBEL.S_ORDER_ITEM, SIEBEL.S_ORDER,
    SIEBEL.S_PROD_INT, SIEBEL.S_CONTACT, SIEBEL.S_CONTACT_X
    WHERE (SIEBEL.S_PROD_INT.NAME<>'Chocolate Chip Crunch - Carton'
     AND SIEBEL.S_PROD_INT.NAME<>'Chocolate Chip Crunch - Sample'
     AND SIEBEL.S_PROD_INT.NAME<>'Peanut Butter Crunch - Carton'
     AND SIEBEL.S_PROD_INT.NAME<>'Peanut Butter Crunch - Sample'
     AND SIEBEL.S_ORDER.X_DURA_CC_DATE_SHIPPED BETWEEN Forms![frmSelection]![txtOrderFromDate] AND Forms![frmSelection]![txtOrderToDate])
     AND  ((SIEBEL.S_ORDER_ITEM.ORDER_ID=SIEBEL.S_ORDER.ROW_ID)
     AND (SIEBEL.S_CONTACT.ROW_ID=SIEBEL.S_ORDER.CONTACT_ID)
     AND (SIEBEL.S_ORDER_ITEM.PROD_ID=SIEBEL.S_PROD_INT.ROW_ID)
     AND (SIEBEL.S_CONTACT_X.PAR_ROW_ID=SIEBEL.S_CONTACT.ROW_ID))   The following code does work: SELECT ALL SIEBEL.S_CONTACT.LAST_NAME, SIEBEL.S_CONTACT.FST_NAME, SIEBEL.S_ORDER_ITEM.ORDER_ID,
    SIEBEL.S_ORDER.CONTACT_ID, SIEBEL.S_ORDER.STATUS_CD, SIEBEL.S_CONTACT_X.ATTRIB_42,
    SIEBEL.S_ORDER.X_DURA_DOCTOR, SIEBEL.S_ORDER.X_DURA_DOCTOR_PHONE,
    SIEBEL.S_ORDER.X_DURA_DISPENSING_PHARMACY, SIEBEL.S_ORDER.X_DURA_PHARMACY_ORDER_NUMBER,
    SIEBEL.S_ORDER.X_DURA_PRINT_FLAG, SIEBEL.S_PROD_INT.NAME,
    SIEBEL.S_ORDER_ITEM.QTY_REQ, SIEBEL.S_ORDER_ITEM.QTY_SHIPPED, SYSDATE-135 FromDate,
    SYSDATE-80 ToDate, SIEBEL.S_ORDER.ORDER_DT, SIEBEL.S_CONTACT.LAST_NAME||', '||SIEBEL.S_CONTACT.FST_NAME FullName,
    SIEBEL.S_CONTACT.HOME_PH_NUM,
    SIEBEL.S_ORDER.X_DURA_CC_DATE_SHIPPED, SIEBEL.S_CONTACT.INTEGRATION_ID
    FROM SIEBEL.S_ORDER_ITEM, SIEBEL.S_ORDER,
    SIEBEL.S_PROD_INT, SIEBEL.S_CONTACT, SIEBEL.S_CONTACT_X
    WHERE (SIEBEL.S_PROD_INT.NAME<>'Chocolate Chip Crunch - Carton'
     AND SIEBEL.S_PROD_INT.NAME<>'Chocolate Chip Crunch - Sample'
     AND SIEBEL.S_PROD_INT.NAME<>'Peanut Butter Crunch - Carton'
     AND SIEBEL.S_PROD_INT.NAME<>'Peanut Butter Crunch - Sample'
     AND SIEBEL.S_ORDER.X_DURA_CC_DATE_SHIPPED BETWEEN SYSDATE-135 AND SYSDATE-80)
     AND  ((SIEBEL.S_ORDER_ITEM.ORDER_ID=SIEBEL.S_ORDER.ROW_ID)
     AND (SIEBEL.S_CONTACT.ROW_ID=SIEBEL.S_ORDER.CONTACT_ID)
     AND (SIEBEL.S_ORDER_ITEM.PROD_ID=SIEBEL.S_PROD_INT.ROW_ID)

I'm not sure if I understand what you mean.

Can you clarify by a coding example?

The question was attached to my original reply.

This is just one example on how you could do this.

Sub ChangeQuery()
Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("PassThroughQuery")
strSQL = "Select * from tblEmployees where "
strSQL = strSQL & "[LastName] = '" & Forms!frmEmployees!LastName & "' "
strSQL = strSQL & " and [FirstName] = '" & Forms!frmEmployees!FirstName & "' "
qdf.SQL = strSQL
Set qdf = Nothing
Set dbs = Nothing
End Sub

--
HTH

Chris Nebinger

  I'm not sure if I understand what you mean.
  Can you clarify by a coding example?

  The question was attached to my original reply.

     I have not seen the question, however, in code you can update the querydef with the value from txtOrderFromDate and txtOrderToDate, then run the pass-through query.  
    --
    Regards,
    Patrick McCarthy  

      The definition of a Pass Through query is one that is NOT manipulated by Access or the Jet Engine at all.  Its sent directly to the server where its run, and Access then reads the output and formats it.

      Oracle is seeing the "Forms![frmSelection]![txtOrderFromDate] AND Forms![frmSelection]![txtOrderToDate])" and doesn't know what you mean, so it returns an error.  The best thing you can do is run a Stored Procedure with variables for the two dates, and call the Store Proc from a Pass Through query created in vba.  Either that or create the whole query in vba.

      I'm sorry to break the bad news, but these are the only ways to do it.  Or at least the only ways I know, and I've been searching for a couple of weeks.  If you find a better way than this let me know.

      Spencer

        I have a SQL Pass-through query, which I want to take variables from a form.The following code does not work:SELECT ALL SIEBEL.S_CONTACT.LAST_NAME, SIEBEL.S_CONTACT.FST_NAME, SIEBEL.S_ORDER_ITEM.ORDER_ID,
        SIEBEL.S_ORDER.CONTACT_ID, SIEBEL.S_ORDER.STATUS_CD, SIEBEL.S_CONTACT_X.ATTRIB_42,
        SIEBEL.S_ORDER.X_DURA_DOCTOR, SIEBEL.S_ORDER.X_DURA_DOCTOR_PHONE,
        SIEBEL.S_ORDER.X_DURA_DISPENSING_PHARMACY, SIEBEL.S_ORDER.X_DURA_PHARMACY_ORDER_NUMBER,
        SIEBEL.S_ORDER.X_DURA_PRINT_FLAG, SIEBEL.S_PROD_INT.NAME,
        SIEBEL.S_ORDER_ITEM.QTY_REQ, SIEBEL.S_ORDER_ITEM.QTY_SHIPPED, SYSDATE-135 FromDate,
        SYSDATE-80 ToDate, SIEBEL.S_ORDER.ORDER_DT, SIEBEL.S_CONTACT.LAST_NAME||', '||SIEBEL.S_CONTACT.FST_NAME FullName,
        SIEBEL.S_CONTACT.HOME_PH_NUM,
        SIEBEL.S_ORDER.X_DURA_CC_DATE_SHIPPED, SIEBEL.S_CONTACT.INTEGRATION_ID
        FROM SIEBEL.S_ORDER_ITEM, SIEBEL.S_ORDER,
        SIEBEL.S_PROD_INT, SIEBEL.S_CONTACT, SIEBEL.S_CONTACT_X
        WHERE (SIEBEL.S_PROD_INT.NAME<>'Chocolate Chip Crunch - Carton'
         AND SIEBEL.S_PROD_INT.NAME<>'Chocolate Chip Crunch - Sample'
         AND SIEBEL.S_PROD_INT.NAME<>'Peanut Butter Crunch - Carton'
         AND SIEBEL.S_PROD_INT.NAME<>'Peanut Butter Crunch - Sample'
         AND SIEBEL.S_ORDER.X_DURA_CC_DATE_SHIPPED BETWEEN Forms![frmSelection]![txtOrderFromDate] AND Forms![frmSelection]![txtOrderToDate])
         AND  ((SIEBEL.S_ORDER_ITEM.ORDER_ID=SIEBEL.S_ORDER.ROW_ID)
         AND (SIEBEL.S_CONTACT.ROW_ID=SIEBEL.S_ORDER.CONTACT_ID)
         AND (SIEBEL.S_ORDER_ITEM.PROD_ID=SIEBEL.S_PROD_INT.ROW_ID)
         AND (SIEBEL.S_CONTACT_X.PAR_ROW_ID=SIEBEL.S_CONTACT.ROW_ID))   The following code does work: SELECT ALL SIEBEL.S_CONTACT.LAST_NAME, SIEBEL.S_CONTACT.FST_NAME, SIEBEL.S_ORDER_ITEM.ORDER_ID,
        SIEBEL.S_ORDER.CONTACT_ID, SIEBEL.S_ORDER.STATUS_CD, SIEBEL.S_CONTACT_X.ATTRIB_42,
        SIEBEL.S_ORDER.X_DURA_DOCTOR, SIEBEL.S_ORDER.X_DURA_DOCTOR_PHONE,
        SIEBEL.S_ORDER.X_DURA_DISPENSING_PHARMACY, SIEBEL.S_ORDER.X_DURA_PHARMACY_ORDER_NUMBER,
        SIEBEL.S_ORDER.X_DURA_PRINT_FLAG, SIEBEL.S_PROD_INT.NAME,
        SIEBEL.S_ORDER_ITEM.QTY_REQ, SIEBEL.S_ORDER_ITEM.QTY_SHIPPED, SYSDATE-135 FromDate,
        SYSDATE-80 ToDate, SIEBEL.S_ORDER.ORDER_DT, SIEBEL.S_CONTACT.LAST_NAME||', '||SIEBEL.S_CONTACT.FST_NAME FullName,
        SIEBEL.S_CONTACT.HOME_PH_NUM,
        SIEBEL.S_ORDER.X_DURA_CC_DATE_SHIPPED, SIEBEL.S_CONTACT.INTEGRATION_ID
        FROM SIEBEL.S_ORDER_ITEM, SIEBEL.S_ORDER,
        SIEBEL.S_PROD_INT, SIEBEL.S_CONTACT, SIEBEL.S_CONTACT_X
        WHERE (SIEBEL.S_PROD_INT.NAME<>'Chocolate Chip Crunch - Carton'
         AND SIEBEL.S_PROD_INT.NAME<>'Chocolate Chip Crunch - Sample'
         AND SIEBEL.S_PROD_INT.NAME<>'Peanut Butter Crunch - Carton'
         AND SIEBEL.S_PROD_INT.NAME<>'Peanut Butter Crunch - Sample'
         AND SIEBEL.S_ORDER.X_DURA_CC_DATE_SHIPPED BETWEEN SYSDATE-135 AND SYSDATE-80)
         AND  ((SIEBEL.S_ORDER_ITEM.ORDER_ID=SIEBEL.S_ORDER.ROW_ID)
         AND (SIEBEL.S_CONTACT.ROW_ID=SIEBEL.S_ORDER.CONTACT_ID)
         AND (SIEBEL.S_ORDER_ITEM.PROD_ID=SIEBEL.S_PROD_INT.ROW_ID)