Microsoft KB Archive/79268

{| = Cannot Use a Function as a Condition in a Q+E Query =
 * width="100%"|

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 &quot;Left&quot; 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): &quot;Q+E for Microsoft Excel User's Guide,&quot; version 3.0, pages 12-14, 131-133
 * }