Cannot Use a Function as a Condition in a Q+E Query

Last reviewed: November 3, 1994
Article ID: Q79268

Summary:

The Select Add Condition command in Q+E is unable to process functions such as DATE or LEFT for the value in its dialog box. If a function is entered in the Value box, Q+E will try to interpret the text as the literal value, which may or may not be a valid query. In most cases, some type of invalid query warning will be displayed.

To use this type of function in a query, you must add it through the Select SQL Query dialog box. For example:

   SELECT Name, DateEntered, Amount FROM sales.dbf
   WHERE DateEntered = Date()

More Information:

Q+E checks to see if the value entered in the Add Condition dialog box is the same type or can be converted to the same type as the column that it is testing against. If it can convert it, then it will use that value; however, it will not calculate a formula entered there. So the LEFT function would indicate to Q+E that it should display all the records in which field is equal to the word "Left" followed by an open and close parenthesis.

Example

A more complete example of how to use a function is as follows:

Suppose you want to select the names of all the employees in EMP.DBF that were hired this month.

In Q+E, the function (DATE()-DAY(DATE())+1) will calculate the first day of the current month.

For this example, change the hire dates for the following employees in EMP.DBF to a day of the current month you are in:

   Tyler Bennett
   John Rappl

Open EMP.DBF in Q+E.

Select all the fields except FIRST_NAME, LAST_NAME and HIRE_DATE.

From the Layout menu, choose Remove Field.

Select the field HIRE_DATE.

From the Select menu, choose Add Condition.

Select Greater or Equal.

Modify the Value Box in the Add Condition dialog box to:

      DATE()-DAY(DATE())+1)

Choose OK.

You will get the Warning message: Missing separator in date field:

To create the proper query:

Perform steps 1-3 above.
From the Select menu, choose SQL Query.

Modify the query to contain:

      SELECT FIRST_NAME, LAST_NAME, HIRE_DATE FROM EMP.DBF
      WHERE HIRE_DATE >= (DATE()-DAY(DATE())+1);

Choose OK.

The query will report all the employees who were hired from the first day of the current month to the current date. The resulting query window will show you something like the following:

   Tyler     Bennett     12/1/91
   John     Rappl     12/13/91

Reference(s):

"Q+E for Microsoft Excel User's Guide," version 3.0, pages 12-14, 131-133

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.