Microsoft KB Archive/117218

= How to Return the Maximum Value in a Database Field =

Article ID: 117218

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * 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.5b
 * Microsoft FoxPro 2.5a
 * Microsoft FoxPro 2.5b
 * Microsoft FoxPro 2.6 Standard Edition
 * Microsoft FoxPro 2.5b for Macintosh
 * Microsoft Visual FoxPro 2.5c for Macintosh

-



This article was previously published under Q117218



SUMMARY
FoxPro does not provide a function for returning the maximum value stored in a field in a database. However, FoxPro does provide the command verb CALCULATE expr list> MAX TO memvar. Although this command is useful, you may need to call a function that will find and return the maximum value in a field for a given condition. To do this, you can use the sample code shown below, which returns the maximum value in a field in any specified table for any given condition.

NOTE: There are several ways of writing this function; this is only one example of how to write it.



MORE INFORMATION
**************************************************************  *                                                            *   *  Function:     DBMAX                                     * * Parameters:                                               * *     fieldname   C   Required                              * *     workarea    N/C Optional                              * *     condition   C   Optional                              * *                                                           *   *                                                            *   *  Purpose: Returns the highest value of any field in the    * *          current or specified work area for any logical   * *          condition. *  *                                                            *   *  NOTE: When you are finding the maximum of a field that is * * not in the current work area, the alias must be supplied  * * in the first parameter. *  **************************************************************

PARAMETERS fieldname, workarea, condition

**************************************************************  * Store parameter count and current work area to memory * variables. **************************************************************

STORE PARAMETERS TO parms STORE SELECT TO currselect

**************************************************************  * Store current record and total records to memory variables. * Initialize m.max memory variable. **************************************************************

reccount = RECCOUNT(IIF(parms>1,workarea,currselect)) currecord = RECNO(IIF(parms>1,workarea,currselect)) m.max = 0

**************************************************************  * Select the correct work area if not current work area. **************************************************************

IF parms > 1 SELECT (workarea) ENDIF

**************************************************************  * Position cursor at top of file. Begin maximum loop. **************************************************************

GO TOP SCAN FOR IIF(parms > 2,EVALUATE(condition),.T.) **************************************************************  * Use a SCAN loop to move through the database and evaluate * the previous record with the current record and return the * greater value. **************************************************************     SKIP -1 STORE EVALUATE(fieldname) TO m.oldmax m.max = MAX(EVALUATE(fieldname),m.max,m.oldmax) SKIP 1 SET MESSAGE TO ALLTRIM(STR(m.max,10,2)) ENDSCAN

**************************************************************  * NOTE: The SCAN loop above could be replaced by the following * code: *  *    CALCULATE FOR IIF(parms > 2,EVALUATE(condition),.T.) **************************************************************

**************************************************************  * Reset record pointer. **************************************************************

DO CASE CASE currecord > reccount GO BOTTOM SKIP 1 CASE currecord < reccount GO TOP SKIP -1 OTHERWISE GO currecord ENDCASE

**************************************************************  * Select the original work area if necessary. **************************************************************

IF parms > 1 SELECT (currselect) ENDIF

SET MESSAGE TO  RETURN m.max ENDIF

The following is an example of how to use this function:

USE customer IN 1 SELECT 0 ? dbmax("customer.ytdpurch",1,"state = 'NC'")

Additional query words: VFoxWin FoxWin FoxDos math user- defined function UDF

Keywords: kbcode KB117218

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.