The LA Fox Developer Newsletter

FoxPro 2X .

Unsupported Trick To Get

Blank (Not Zero) Fields

By Rorschach (wsd@world.std.com)

I been pulling my hair out for hours trying to figure this out. Is is possible to exclude records from an average that are blank?

DOCUMENT: Q97647 14-DEC-I 993 [B_WFOXPRO]

TITLE: How Exclude Blanks, but Not Zeros, from an Average

PRODUCT: FoxPro For Windows

PRODNER: 2.50 2.50a / 2.00 2.50 2.50a

OPER/SYS: WiNDOWS! MS-DOS

The information in this article applies to:

Microsoft FoxPro for Windows, versions 2.5 and 2.5a

Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, and 2.5a

Summary:

FoxPro’s average command sums numeric fields and divides the summed result by the number of records summed. In some cases, you may want to determine the average only for fields that are not blank. Since there is no command in the FoxPro language that can distinguish between a numeric field that is blank and one that contains a zero, you must write a user- defined function similar to the one below in order to accomplish this task.

More Information:

The following function requires two parameters, the database name and field name. An example of calling this function is as follows:

ave = ave_nobl("test", “num”)

The above command shows test as the name of the database file and num as the numeric field to be

averaged.

Sample Code:

FUNCTION ave_nobl

PARAMETERS dbfname,fld_name

PRIVATE blank,sklpbytes,l,numofblanks

skipbytes=0

numofblanksO

IF USED(dbfname)

SELECT (dbfname)

ELSE

SELECT 0

USE (dbfname)

ENDIF

June 1997

headersize = HEADER(dbfname)

no_records = RECCOUNTO

rec_size = RECSIZE()

fld_width = FSIZE(fld_name)

Place the database structure information in an array to determine the field’s position. The position is equal to the row number of the field in the array, which is returned by asubscriptO.

fld..pos = ASUBSCRIPT(afld_pos, ASCAN(afld_pos, UPPER(fld_name)), 1)

FOR I = I TO (fld.pos - 1)

sklpbytes = skipbytes + FSIZE(FIELD(i))

ENOFOR

use

blank REPLICATE(” “, fld_width)

m.file = FOPEN(dbfname + “.dbf’)

IF m.file < 0

RETURN “Error opening database” + dbfname + “.‘

The numeric field begins on the first byte following the header and any preceding fields (headersize +

skipbytes + I).

m.move = FSEEK(m.flle, headersize + sklpbytes + 1)

Read the value of the numeric field and compare it to the

variable blank.

m.num = FREAD(m.file, fld_wldth)

DO WHILE .NOT. FEOF(m.file)

IF m.num = blank

numofblanks = numofblanks+1

ENDIF

Move to the beginning of the numeric field in the next record, read the next value, and compare itto the variable blank.

m.move FSEEK(m.file, (rec_slze - fld_wldth), 1) m.num = FREAD(m.file, fld_wldth)

ENDDO

m.move — FCLOSE(m.file)

USE (dbfname)

SUM EVAL(fld_name) TO sub_result

USE

Divide the sum by the number of records that are not blank.

result = sub_result / (no_records - numofblanks)

Return the result to the program that called ave_nobl().
(Cont, page 7)

Page 6