Q+E UAE with % Search on Date column With SQLServer Driver

 Last reviewed: November 3, 1994
Article ID: Q76294
Summary:

Q+E version 3.0 will return an Unrecoverable Application Error (UAE) when you make a query using the wildcard "%" on a column containing DATETIME data with the SQLServer driver.

This occurs only with Q+E for Microsoft Excel for Windows version 3.0 dated 12-9-90.

Steps to Reproduce Problem

  1. Logon to SQL database and select a table that contains a date field.

  2. Select the date column.

  3. Choose the Add Condition button.

  4. In the Value box, type "%/%/87" or "%%/%%/87" without the quotation marks to find all dates in 1987.

  5. Choose the OK button.

You will receive an Unrecoverable Application Error message.

More Information:

A UAE will result whether you have the options DOS or SQL wildcards selected. After the UAE, if you try to Logon to the SQL server again you will get another error message "Null DB process Encountered". You must restart Windows.

Workaround

You may add any of the following WHERE clauses to your SQL Query to find those records with dates that meet your criteria.

Method #1: 

   WHERE DATECOL BETWEEN 1/1/1987 AND 12/31/1987
Records matching the WHERE clause criteria values are included in the results.

Method #2: 

   WHERE SUBSTRING(CONVERT(CHAR(20),DATECOLUMN),8,4 = "1987"
This function converts the DATE datatype to a 20 char text string and then extracts 4 characters starting at char #8 to use for comparison with "1987".

Method #3: 

   WHERE DATEPART(DATECOL,YY) = 1987
YY can be substituted with MM,DD,YY,HH,MI or SS

The first option is probably easier to use and, if DATECOL is indexed, it will use the index and be faster. The second method allows you more flexibility for substituting the different date components, and will use wildcards. The third method is probably the best "intended use" of the features and can work with any of the DATETIME components.

KBCategory: kbother
KBSubcategory:

Additional reference words: 3.00

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: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.