Sample Code for Summing a Field in a Database

ID: Q114253

2.50 2.50a 2.50b 2.60 3.00 | 2.00 2.50 2.50a 2.50b 2.60 | 2.50b

WINDOWS                    | MS-DOS                     | MACINTOSH

• Microsoft Visual FoxPro for Windows, version 3.0
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro for Macintosh, version 2.5b

SUMMARY

FoxPro does not provide a function that sums a field in a database. The closest FoxPro comes is the command SUM <field> 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

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

Keywords          : kbcode FxenvMemory 
Version           : 2.50 2.50a 2.50b 2.60 3.00 | 2.0
Platform          : MACINTOSH MS-DOS WINDOWS