FIX: Calculate Lowest on Date Field in Group Returns Number

The information in this article applies to:

• Microsoft FoxPro for Windows, versions 2.5x, 2.6x

SYMPTOMS

When you are using the Calculate Lowest option on a date field that is in the Group band of a report, the lowest date in that grouping should be returned, but a number is returned instead.

CAUSE

Calculate Lowest is returning the Julian date instead of the character type date.

RESOLUTION

Workaround 1

To work around this problem, create the following user-defined function (UDF):

1. Create a field in the Group band.

2. In the field, type:

         MYUDF()
   

3. Create a program named MYUDF.PRG that contains the following commands:

         mrecno=RECNO()
         CALCULATE MIN(datefield) TO x
         GOTO mrecno
         RETURN x
   

Workaround 2

1. From the Report Menu, choose Variables.

2. Choose Add.

3. For the Variable Name, type "mindate" (without the quotation marks).

4. For the value you want to store, select the date field for which you want the minimum value.

5. For the Initial Value, select the date field again.

6. Choose Lowest.

7. At the end of the report, choose Reset. Or, if you are using a group, choose Group.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Visual FoxPro 3.0 for Windows.

MORE INFORMATION

Steps to Reproduce Problem

1. Create a database with a character field called NAME and a date field called ZDATE.

2. Enter the following three records:

          NAME           ZDATE
          -----------------------
   
          Paul           01/19/19
          Paul           04/28/68
          Paul           05/15/93
   
   

3. Save the database.

4. From the File menu, choose New, select Report, and then choose New.

5. From the Report menu, choose Quick Report, and choose OK.

6. From the Report menu, choose Data Grouping.

7. Choose Add and select the date field to group on. Choose OK three times.

8. Stretch the group footer to open a line.

9. Click the ZDATE field in the Detail band.

10. From the Edit menu, choose Copy.

11. From the Edit menu, choose Paste.

12. Move the new ZDATE field into the Group Footer band.

13. Double-click the new ZDATE field in the Group Footer band.

14. Select Calculate, select Lowest, and then choose OK twice.

15. From the Report menu, choose Page Preview, and then choose Zoom In.