DOCUMENT:Q271492  27-JUL-2001  [foxpro]
TITLE   :PRB: EVAL() Function in WHERE Clause May Return Incorrect Data
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:3.0,3.0b,5.0,5.0a,6.0
OPER/SYS:
KEYWORDS:kbDatabase kbSQL kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGrpDSFox kbD

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

When you use the EVALUATE() function in the WHERE clause of a SELECT - SQL
command, Visual FoxPro may fail to return all matching rows. This may happen if
there are matching rows but the record pointer is not on one of them at the time
the SELECT is run.

RESOLUTION
==========

For standard SELECT statements, this can be solved by using macro substitution
instead of the EVALUATE() function, or by doing a LOCATE before the SELECT, as
shown in the "Steps to Reproduce Behavior" section.

For views, the following syntax has been shown to work in some cases. First, set
up the database as shown in "Steps to Reproduce Behavior," and then run the
following code from the Command window or a program file:

   lcCriteria = '.T.'
   CREATE SQL VIEW macroTestView AS ;
      SELECT * ;
         FROM testTable ;
         WHERE &?lcCriteria

   CLEAR

   USE macroTestView
   ? "All records"
   LIST

   lcCriteria = "BETWEEN(testTable.code, 'a', 'c')"
   ? lcCriteria
   =REQUERY()
   LIST

   lcCriteria = "BETWEEN(testTable.code, 'd', 'f')"
   ? lcCriteria
   =REQUERY()
   LIST

MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

Run the following code from the Command window or a program file to see the
preceding behavior:

   CREATE DATABASE testDBC
   CREATE TABLE testTable (code C(4))

   INSERT INTO testTable VALUES ("a")
   INSERT INTO testTable VALUES ("b")
   INSERT INTO testTable VALUES ("c")
   INSERT INTO testTable VALUES ("d")
   INSERT INTO testTable VALUES ("e")
   INSERT INTO testTable VALUES ("f")
   INSERT INTO testTable VALUES ("g")
   INSERT INTO testTable VALUES ("h")
   INSERT INTO testTable VALUES ("i")

   GO TOP
   CLEAR

   lcCriteria = 'BETWEEN(code, "a", "c")'
   ? lcCriteria
   SELECT * ;
      FROM testTable ;
      INTO CURSOR crsr1 ;
      WHERE EVALUATE(lcCriteria)
   LIST
   *!* Three records are displayed

   lcCriteria = 'BETWEEN(code, "d", "f")'
   ? lcCriteria

   SELECT * ;
      FROM testTable ;
      INTO CURSOR crsr2 ;
      WHERE EVALUATE(lcCriteria)

   IF _TALLY > 0
      LIST
   ELSE
      ? "   No records found."
   ENDif
   *!* No records are displayed.
   *!* You would expect three records to be returned. 

   SELECT testTable
   LOCATE FOR EVALUATE(lcCriteria)
   ? lcCriteria + ": LOCATEd first"

   SELECT * ;
      FROM testTable ;
      INTO CURSOR crsr3 ;
      WHERE EVALUATE(lcCriteria)
   LIST
   *!* Correct records are displayed

REFERENCES
==========

For additional information about using the EVALUATE() function and the SELECT -
SQL statement, click the article numbers below to view the articles in the
Microsoft Knowledge Base:

   Q125046 INFO: Macro Substitution, Name Expressions, and EVALUATE() Runtime
   Evaluation

   Q114670 HOWTO: Pass Variables to SELECT-SQL w/o Using Macro Substitution

   Q193079 FIX: Syntax Error with EVAL of ALL or LTRIM in SELECT-SQL

Additional query words:

======================================================================
Keywords          : kbDatabase kbSQL kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGrpDSFox kbDSupport kbCodeSnippet kbSQLProg 
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a
Version           : WINDOWS:3.0,3.0b,5.0,5.0a,6.0
Issue type        : kbprb
Solution Type     : kbpending

=============================================================================

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.

Copyright Microsoft Corporation 2001.