DOCUMENT:Q194780  06-AUG-1999  [foxpro]
TITLE   :HOWTO: Requery a SQL-Select Only Changing a Variable
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:5.0a,6.0
OPER/SYS:
KEYWORDS:kbvfp500a kbvfp600

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

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

SUMMARY
=======

This article shows how to create a SQL-Select statement and obtain a different
result set by changing the value of the variable in the SQL WHERE clause.

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

A common assumption is that to obtain a new result set you must reissue the SQL
query. This is not always the case if you have an index set defined on the field
used in the WHERE clause. It does not matter if the field in the WHERE clause is
part of a compound or single field index.

The reason Visual FoxPro is able to return a different result set is that Visual
FoxPro internally sets a FILTER on the indexed field in the WHERE clause.

The following code demonstrates how changing a variable then issuing a BROWSE
changes the result set. The code uses the Customer table that has an index tag
on the country field. If there is no index that contains country, the result set
does not update. In Visual FoxPro 5.0a, use the Customer table in the
\vfp5a\samples\data\ directory. If using Visual FoxPro 6.0, use the Customer
table in the "c:\program files\microsoft visual
studio\msdn98\98vs\1033\samples\vfp98\data\ directory.

Sample Code
-----------

      ***** Begin code sample *****
      IF "5.00"$VERSION()
         dbfpath = HOME()+"samples\data\"
      ELSE
         dbfpath = HOME(2)+"data\"
      ENDIF
      CLEAR
      PUBLIC acountry(1),i
      mcountry=" "
      SELECT DISTINCT(country) ;
         FROM dbfpath+"customer" ;
         WHERE !EMPTY(country) ;
         ORDER BY country ;
         INTO ARRAY acountry

      SELECT company,contact,city,country ;
         FROM customer ;
         WHERE UPPER(country)=mcountry ;
         INTO CURSOR QUERY
      BROWSE NOWAIT
      WAIT WINDOW "Hit a key to see next country..." TIME .5

      FOR i= 2 TO ALEN(acountry)
         mcountry=UPPER(acountry(i))
         BROWSE NOWAIT
         WAIT WINDOW "Hit a key to see next country..." TIME .5
      ENDFOR
      RELEASE WINDOW QUERY
      ***** End code sample *****

REFERENCES
==========

(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Dean
Christopher, Microsoft Corporation.


Additional query words:

======================================================================
Keywords          : kbvfp500a kbvfp600 
Technology        : kbVFPsearch kbAudDeveloper kbVFP600 kbVFP500a
Version           : WINDOWS:5.0a,6.0
Issue type        : kbhowto

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

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 1999.