Microsoft KB Archive/271492

= PRB: EVALUATE Function in WHERE Clause May Return Incorrect Data =

Article ID: 271492

Article Last Modified on 5/12/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q271492



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 &quot;Steps to Reproduce Behavior&quot; section.

For views, the following syntax has been shown to work in some cases. First, set up the database as shown in &quot;Steps to Reproduce Behavior,&quot; 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 ? &quot;All records&quot; LIST

lcCriteria = &quot;BETWEEN(testTable.code, 'a', 'c')&quot; ? lcCriteria =REQUERY LIST

lcCriteria = &quot;BETWEEN(testTable.code, 'd', 'f')&quot; ? lcCriteria =REQUERY LIST



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 (&quot;a&quot;) INSERT INTO testTable VALUES (&quot;b&quot;) INSERT INTO testTable VALUES (&quot;c&quot;) INSERT INTO testTable VALUES (&quot;d&quot;) INSERT INTO testTable VALUES (&quot;e&quot;) INSERT INTO testTable VALUES (&quot;f&quot;) INSERT INTO testTable VALUES (&quot;g&quot;) INSERT INTO testTable VALUES (&quot;h&quot;) INSERT INTO testTable VALUES (&quot;i&quot;)

GO TOP CLEAR

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

lcCriteria = 'BETWEEN(code, &quot;d&quot;, &quot;f&quot;)' ? lcCriteria

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

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

SELECT testTable LOCATE FOR EVALUATE(lcCriteria) ? lcCriteria + &quot;: LOCATEd first&quot;

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

