Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Excel Query issues in Excel 2010

Answered Excel Query issues in Excel 2010

  • Monday, March 12, 2012 3:48 PM
     
     
    Sign In to Vote
    0

    All,

    I have a workbook that runs numerious queries against an Oracle DB. The workbook was created in Excel 2003 and still runs fine in that enviornment. When run in 2010 however there seems to be issues with the dates that are being pased as parameters. The queries all mimic the one below but run for different date ranges..... one for Jan..... one for Feb.... etc.

    SELECT  WORK_ORDER_MASTER.WOM_EQP_ID,
     WORK_ORDER_MASTER.WOM_WO_ID,
     WORK_ORDER_MASTER.WOM_WO_DSC,  WORK_ORDER_MASTER.MM_DESC_OF_PROBLEM,  WORK_ORDER_MASTER.MM_HEADER_INSTRUCTIONS,  WORK_ORDER_MASTER.MM_COMPLETION_REMARKS,  WORK_ORDER_MASTER.WOM_CMPL_DTE, WORK_ORDER_MASTER.WOM_CAU_ID,  WORK_ORDER_MASTER.MM_MTBF_FLAG
    FROM  MANUFACTURING.WORK_ORDER_MASTER WORK_ORDER_MASTER
    WHERE  (WORK_ORDER_MASTER.WOM_EQP_ID>='242' And  WORK_ORDER_MASTER.WOM_EQP_ID<='730' And  WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'LT' And  WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'PM' And  WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'PD') AND  (WORK_ORDER_MASTER.WOM_CMPL_DTE>? And  WORK_ORDER_MASTER.WOM_CMPL_DTE<?) AND  (WORK_ORDER_MASTER.MM_MTBF_FLAG='F')
    ORDER  BY WORK_ORDER_MASTER.WOM_EQP_ID, WORK_ORDER_MASTER.WOM_CMPL_DTE

    The problem seems to be with the

    (WORK_ORDER_MASTER.WOM_CMPL_DTE>? And  WORK_ORDER_MASTER.WOM_CMPL_DTE<?)

    statement in the WHERE clause. Each spreadsheet has 2 parameters

    BeginDate    =$A$2

    EndDate       =$B$2

    When run in 2003 it runs fine and all the appropriate data is pulled. When run in 2010 I get

    [Oracle][ODBC][Ora]ORA-01847: day of month must be between 1 and last day of month

    Nothing has changed on my backend. Just running Excel 2010 now in replace of 2003. Dates are in the '2/1/2012' format.

    Thanks,

    Steve

     

All Replies

  • Tuesday, March 13, 2012 1:03 PM
     
     
    Sign In to Vote
    0

    Hi

    Have you tried using

    BeginDate =TEXT($A$2,"dd/mm/yyyy")  - use date format as applicable

    EndDate =TEXT($B$2,"dd/mm/yyyy")

    to ensure that the query passes a text version of the date and not a number.

    Hope this helps.

    G North MCT

     
  • Tuesday, March 13, 2012 2:22 PM
     
     
    Sign In to Vote
    0

    Thanks for the response. No such luck however. Getting another error now

    [Oracle][ODBC][Ora]ORA-00911: invalid character

    This is the result of running

    SELECT WORK_ORDER_MASTER.WOM_EQP_ID, WORK_ORDER_MASTER.WOM_WO_ID, WORK_ORDER_MASTER.WOM_WO_DSC, WORK_ORDER_MASTER.MM_DESC_OF_PROBLEM, WORK_ORDER_MASTER.MM_HEADER_INSTRUCTIONS, WORK_ORDER_MASTER.MM_COMPLETION_REMARKS, WORK_ORDER_MASTER.WOM_CMPL_DTE, WORK_ORDER_MASTER.WOM_CAU_ID, WORK_ORDER_MASTER.MM_MTBF_FLAG
    FROM MANUFACTURING.WORK_ORDER_MASTER WORK_ORDER_MASTER
    WHERE (WORK_ORDER_MASTER.WOM_EQP_ID>='242' And WORK_ORDER_MASTER.WOM_EQP_ID<='730' And WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'LT' And WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'PM' And WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'PD') AND (WORK_ORDER_MASTER.WOM_CMPL_DTE>TEXT($A$2,"dd/mm/yyyy") And
    WORK_ORDER_MASTER.WOM_CMPL_DTE<TEXT($B$2,"dd/mm/yyyy")) AND (WORK_ORDER_MASTER.MM_MTBF_FLAG='F')
    ORDER BY WORK_ORDER_MASTER.WOM_EQP_ID, WORK_ORDER_MASTER.WOM_CMPL_DT

    This is when

    BeginDate = text field "02/01/2012"

    EndDate = text field "02/28/2012"

     
  • Tuesday, March 13, 2012 8:10 PM
     
     
    Sign In to Vote
    0

    So the below code works

    SELECT WORK_ORDER_MASTER.WOM_EQP_ID, WORK_ORDER_MASTER.WOM_WO_ID, WORK_ORDER_MASTER.WOM_WO_DSC, WORK_ORDER_MASTER.MM_DESC_OF_PROBLEM, WORK_ORDER_MASTER.MM_HEADER_INSTRUCTIONS, WORK_ORDER_MASTER.MM_COMPLETION_REMARKS, WORK_ORDER_MASTER.WOM_CMPL_DTE, WORK_ORDER_MASTER.WOM_CAU_ID, WORK_ORDER_MASTER.MM_MTBF_FLAG
    FROM MANUFACTURING.WORK_ORDER_MASTER WORK_ORDER_MASTER
    WHERE (WORK_ORDER_MASTER.WOM_EQP_ID>='242' And WORK_ORDER_MASTER.WOM_EQP_ID<='730' And WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'LT' And WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'PM' And WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'PD') AND (WORK_ORDER_MASTER.WOM_CMPL_DTE> to_date('2/1/2012','mm/dd/yyyy') And
    WORK_ORDER_MASTER.WOM_CMPL_DTE< to_date('2/28/2012','mm/dd/yyyy')) AND (WORK_ORDER_MASTER.MM_MTBF_FLAG='F')
    ORDER BY WORK_ORDER_MASTER.WOM_EQP_ID, WORK_ORDER_MASTER.WOM_CMPL_DTE

    but I am completely unable to capture the dates from $A$2 and $B$2. Uggggh! Anyone have any idea what I am doing wrong here? Simply want to grab the dates from fields in the sheet.

    Thanks,

    Steve

     
  • Tuesday, March 13, 2012 8:38 PM
     
     Answered
    Sign In to Vote
    0

    Hi

    Glad your making progress.  I would suggest building your statement by concatenation.   Use a string variable to build the full statement and then supply it to the code., eg.

    txtSQL ="SELECT WORK_ORDER_MASTER.WOM_EQP_ID, WORK_ORDER_MASTER.WOM_WO_ID, WORK_ORDER_MASTER.WOM_WO_DSC, WORK_ORDER_MASTER.MM_DESC_OF_PROBLEM, WORK_ORDER_MASTER.MM_HEADER_INSTRUCTIONS, WORK_ORDER_MASTER.MM_COMPLETION_REMARKS, WORK_ORDER_MASTER.WOM_CMPL_DTE, WORK_ORDER_MASTER.WOM_CAU_ID, WORK_ORDER_MASTER.MM_MTBF_FLAG
    FROM MANUFACTURING.WORK_ORDER_MASTER WORK_ORDER_MASTER
    WHERE (WORK_ORDER_MASTER.WOM_EQP_ID>='242' And WORK_ORDER_MASTER.WOM_EQP_ID<='730' And WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'LT' And WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'PM' And WORK_ORDER_MASTER.WOM_EQP_ID Not Like 'PD') AND (WORK_ORDER_MASTER.WOM_CMPL_DTE> to_date('" & FORMAT($A$2,"mm/dd/yyyy")

    txtSQL=txtSQL & "','mm/dd/yyyy') And WORK_ORDER_MASTER.WOM_CMPL_DTE< to_date('" & FORMAT($B$2,"mm/dd/yyyy")

    txtSQL = txtSQL & ") AND (WORK_ORDER_MASTER.MM_MTBF_FLAG='F')
    ORDER BY WORK_ORDER_MASTER.WOM_EQP_ID, WORK_ORDER_MASTER.WOM_CMPL_DTE"

    Be careful with the single and double quotes before and after the &s

    Then you can use it like this:

    With ActiveSheet.QueryTables.Add(Connection:=conString, Destination:=Range("$A$1"),Sql:=txtSQL)

    Hope this helps

    G North MCT