FIX: Calculate Lowest on Date Field in Group Returns Number

ID: Q100567

2.5x 2.6x WINDOWS kbprg kbfixlist kbbuglist

The information in this article applies to:

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.

The result in the group footer is 2421978; it should be 01/19/19. If you type ?SYS(11,'01/19/19') in the Command window, FoxPro returns 2421978, which is the Julian date for 01/19/19.

Additional reference words: FoxWin 2.50 2.50a 2.50b 2.60 2.60a VFoxWin fixlist3.00 buglist2.50 buglist2.50a buglist2.50b buglist2.60 buglist2.60a minimum report KBCategory: kbprg kbfixlist kbbuglist KBSubcategory: FxprgGeneral 

Keywords          : FxprgGeneral kbbuglist kbfixlist
Version           : 2.5x 2.6x
Platform          : WINDOWS
Solution Type     : kbfix

Last Reviewed: September 24, 1997