Microsoft KB Archive/114253

= Sample Code for Summing a Field in a Database =

Article ID: 114253

Article Last Modified on 12/3/2003

-

APPLIES TO


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

-



This article was previously published under Q114253



SUMMARY
FoxPro does not provide a function that sums a field in a database. The closest FoxPro comes is the command SUM TO memvar. Although this command is useful, you may need to call a function that will sum a field for a given condition.

The sample code below is a user-defined function (UDF) that can sum a field in any specified table for any given condition.



MORE INFORMATION
**************************************************************  *                                                            *   *  Function:     DBSUM                                     * * Parameters:                                               * *     fieldname   C   Required                              * *     workarea    N   Optional                              * *     condition   C   Optional                              * *                                                           *   *                                                            *   *   Purpose: Sums any field in the current or specified      * *           work area for any logical condition. *  *                                                            *   *   NOTE: When summing 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

*******************************  * Verify that the field name passed in is a numeric field *******************************

IF TYPE(fieldname) != 'N'     WAIT WINDOW "Data type mismatch" NOWAIT RETURN "" ELSE

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

currecord = RECNO(IIF(parms>1,workarea,currselect)) m.sum = 0

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

IF parms > 1 SELECT (workarea) ENDIF

*******************************  * Position cursor at top of file * Begin summation loop *******************************

GO TOP SCAN FOR IIF(parms > 2,EVALUATE(condition),.T.) m.sum = m.sum + EVALUATE(fieldname) SET MESSAGE TO ALLTRIM(STR(m.sum,10,2)) ENDSCAN

*******************************  * 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.sum ENDIF

To use the DBSUM function, execute the following commands in the Command window or in a program:

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

Additional query words: VFoxWin FoxMac FoxDos FoxWin total

Keywords: kbcode KB114253

-

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

© Microsoft Corporation. All rights reserved.