Microsoft KB Archive/107747

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.


Q+E: DDE or SQL Error Message Extracting Oracle Dates

Last reviewed: September 12, 1996
Article ID: Q107747

kb3rdparty The information in this article applies to:

  • Q+E for Microsoft Excel for Windows, versions 3.0, 3.0a
  • Q+E for Microsoft Excel for OS/2, versions 3.0
  • Microsoft Excel for Windows, versions 3.0 and 4.0
  • Microsoft Excel for OS/2, version 3.0

SYMPTOMS

When you set an external database to an Oracle table and extract dates, you may receive a DDE or SQL error message.

STATUS

Microsoft has confirmed this to be a problem with the Q+E add-in macro. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

WORKAROUNDS

To work around this problem, do either of the following.

Workaround 1

The following procedure should produce the same results you get when you choose Extract from the Data menu. (You can automate this procedure with a macro by recording steps 2-5 with the Macro Recorder.)

  1. In Q+E, open the Oracle Table, choose Select Add Condition and run your Query on the Date field. Save the Query as DATE.QEF.
  2. Start Microsoft Excel.
  3. From the Data menu, choose SQL Query.
  4. Open the DATE.QEF file and choose the Run button.
  5. Paste the records as unlinked.

Workaround 2

Because this problem is the result of a syntax error in the SQL statement, you can correct it by correcting the SQL statement. To view the SQL query statement and correct it in Microsoft Excel, do the following:

  1. In Q+E, open the Oracle Table, choose Select Add Condition and run your Query on the Date field.
  2. Select the SQL query (the SQL statement is already highlighted) and press CTL+INS to copy the query.
  3. In Microsoft Excel, paste the SQL statement into an empty portion of the spreadsheet. It should resemble the following:

          (where Table name = Datephoner Date Field = DATED. SELECT DATED,
          NAME, NUMBERED, PHONERFROM DATEPHONERWHERE DATED > TO_DATE('1992-12-
          04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'
  4. From the Data menu, choose Set Database, select the External option and choose OK.
  5. In the External Database dialog box, in the Source box, choose the Sources button, log on to Oracle, and select your table.
  6. In cell A1, use a substitute field name for your criteria date field. For example, use "Fred" instead of "Date." For the criteria in cell A2, use a portion of the SQL statement that was pasted from Q+E. Select and copy everything after the "where" statement in the above example and paste this into cell A2. Your data should resemble the following example:

          A1: Fred A2: DATED > TO_DATE('1992-12-00:00','YYYY-MM-DD HH24:MI:SS'
  7. Select cells A1:A2 and choose Set Criteria from the Data menu.
  8. Paste the field names, set the extract range and choose Extract from the Data menu.

REFERENCES

"Q+E for Microsoft Excel User's Guide," version 3.0a, included with Microsoft Excel version 3.0 "Q+E for Microsoft Excel User's Guide," version 3.0a, included with Microsoft Excel version 4.0


KBCategory: kb3rdparty

KBSubcategory:

Additional reference words: 3.00 4.00 QE 4 4.00a W_EXCEL



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.