Q136043: How to Allow Empty Fields in Visual FoxPro Crosstab Queries

Article: Q136043
Product(s): Microsoft FoxPro
Version(s): WINDOWS:3.0
Operating System(s): 
Keyword(s): 
Last Modified: 15-FEB-2000

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

- Microsoft Visual FoxPro for Windows, version 3.0 
-------------------------------------------------------------------------------

SUMMARY
=======

Crosstab queries created in FoxPro for Windows version 2.6 allowed empty fields
in the output. In Visual FoxPro, however, the default behavior is to insert
.NULL. as a placeholder where empty fields exist in the output.

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

Step-by-Step Procedure
----------------------

To have Visual FoxPro crosstab queries use empty fields instead of the .NULL.
placeholder, follow these steps:

1. Open Vfpxtab.prg and save it as Myxtab.prg.

2. In Myxtab.prg, search for SET NULL ON and change it to SET NULL OFF.

3. Search for this:

     IF ISNULL(EVAL(FIELD(m.i)))
        LOOP
     ENDIF

  Replace it with this:

     cFieldName = field(m.i)
     IF ISBLANK(&cFieldName)
        LOOP
     ENDIF

4. In the Command window, type the following commands:

      _GENXTAB = "C:\VFP\MYXTAB.PRG"
      MODIFY COMMAND NULLTEST.

5. Place the following code in Nulltest:

         SELECT Customer.city, Customer.contact,;
         COUNT(Customer.maxordamt);
         FROM tastrade!customer;
         GROUP BY Customer.city, Customer.contact;
         ORDER BY Customer.city, Customer.contact;
         INTO CURSOR SYS(2015)
         DO (_GENXTAB) WITH 'Query',.t.,.t.,.t.,,,,.t.,1

         BROWSE NOMODIFY

Additional query words: VFoxWin

======================================================================
Keywords          :  
Technology        : kbVFPsearch kbAudDeveloper kbVFP300
Version           : WINDOWS:3.0

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

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 1986-2002.