SUMMARY
The QE.XLM macro included with Microsoft's Q+E version 2.50 may not
always extract the correct records when the criteria value is a date.
For example, if the external database has a DATE field, and if
">9/13/85" (without the quotation marks) is placed in the criteria
range, then the resulting external extract will only return records
where DATE equals 9/13/85.
MORE INFORMATION
Steps to Reproduce Problem
- Start Excel and load the QE.XLM macro.
- From the Data menu, choose Set Source. Select Login and SQL Server,
and choose OK.
- Input the appropriate Server Name, User ID, and Password. Choose
OK and then choose OK again.
- From the Data menu, choose Set Owner. Choose OK. Select a table
that has a DATE field. Choose OK.
- From the Data menu, choose Paste Field Names. Choose Paste All
and choose OK.
At this point, create the Criteria and Extract range in the normal
manner.
- In the Date field of the Criteria range, input a date such as
">9/13/85" (without the quotation marks).
- From the Data menu, choose Extract External. Select either linked
or unlinked. Choose OK.
Note that only those records that are equal to the criteria date are
listed.
The Cause and Solution to the Problem
- From the Data menu, choose SQL query. Looking at the SQL query
that is created for the extract, the WHERE clause would say:
WHERE [date like 'Sep% 13 1985%'].
- From the Data menu, choose SQL query. To extract the correct
records, the WHERE clause should say:
WHERE [date > '9/13/85']
Changing the clause and then performing the SQL query returns the
correct results.
|