Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/169791

From BetaArchive Wiki

Article ID: 169791

Article Last Modified on 5/12/2003



APPLIES TO

  • Microsoft FoxPro 2.0
  • Microsoft FoxPro 2.5b for MS-DOS
  • Microsoft FoxPro 2.5a
  • Microsoft FoxPro 2.5b for MS-DOS
  • Microsoft FoxPro 2.6 for MS-DOS
  • Microsoft FoxPro 2.6a Standard Edition
  • Microsoft FoxPro 2.5b
  • Microsoft FoxPro 2.5a
  • Microsoft FoxPro 2.5b
  • Microsoft FoxPro 2.6 Standard Edition
  • Microsoft FoxPro 2.6a Standard Edition
  • Microsoft FoxPro 2.5b for Macintosh
  • Microsoft Visual FoxPro 2.5c for Macintosh
  • Microsoft FoxPro 2.6a Professional Edition for Macintosh
  • Microsoft Visual FoxPro 3.0b for Macintosh
  • 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



This article was previously published under Q169791

SYMPTOMS

A query may result with truncated values in an expression columns.

CAUSE

The expression column is basing its width on the first record value of the source table.

RESOLUTION

Change the expression to use an IIF() function that returns a value of necessary width. This forces FoxPro to create a column wide enough to hold the results of the expression column. NOTE: The example code uses the table created at the bottom of this article in the MORE INFORMATION section. Here's an example:

   SELECT *, IIF(ISNULL(fld2),SPACE(15),TTOC(fld2,1)) AS test ;
   FROM temp WHERE fld2 is NOT NULL
                



-or-

Change the source table's order so that the first record's value in the source table is wide enough to hold the expression columns output. An index order cannot be used here, since FoxPro opens the source table in another workarea. Using an additional query can do this. Here's an example:

   SELECT * FROM temp ORDER BY fld2 DESCEND INTO CURSOR temp2
   SELECT *, TTOC(fld2,1) AS test FROM temp2
                

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Execute the following five lines to create and populate a table:

          CREATE TABLE temp (fld1 C(3), fld2 T NULL, fld3 C(3))
          INSERT INTO temp (fld1, fld2, fld3) VALUES ('aaa',NULL,'aaa')
          INSERT INTO temp (fld1, fld2, fld3) VALUES ('bbb',datetime(),'bbb')
          INSERT INTO temp (fld1, fld2, fld3) VALUES ('ccc',NULL,'ccc')
          INSERT INTO temp (fld1, fld2, fld3) VALUES ('ddd',datetime(),'ddd')
  2. Execute the following lines from the Command window:

          SELECT *, TTOC(fld2,1) AS test FROM temp
          SELECT *, TTOC(fld2,1) AS test FROM temp WHERE fld2 IS NOT NULL

Note that the test column comes up improperly sized for both queries. The test column is based solely on the width of the source table's first record, a 1 byte NULL character. Thus, the column shows only a 1 from the result of the TTOC() function.

This occurs even when records that contain NULL are not included in the result set, per the second query.

Keywords: kbprb KB169791