SUM problems 1

[beesman]

Hi,

Been trying to figure this one out, but am pretty stumped - so any help really appreciated.

I have a dynamically created table with a large number of character & numeric fields. I need to sum the total of all numeric fields & have written the following code:

strx = 'SUM '
FOR i = 1 TO FCOUNT()
    IF TYPE(FIELD(i)) = "N"
		strx = strx + FIELD(i)
	    IF i < FCOUNT()
	        strx = strx + '+'
	    ENDIF
    ENDIF 
ENDFOR
strx = strx + &quot; TO total&quot;
&strx[\CODE]

Trouble is that on running it produces an exception code & crashes VFP. Any ideas where I've gone wrong?

TIA

 

[baltman]

You are leaving a hanging '+' sign on the end of your string...

strx = 'SUM '
FOR i = 1 TO FCOUNT()
IF TYPE(FIELD(i)) = "N"
strx = strx + FIELD(i)
IF i < FCOUNT()
strx = strx + '+'
ENDIF
ENDIF
ENDFOR
strx=LEFT(strx,LEN(strx)-1) && remove last '+'<----------
strx = strx + &quot; TO total&quot;
?strx
&strx

Brian

 

[beesman]

Thanks for the reply Brian.

Unfortunately, it's not solved the problem. I think that part of the code is ok, as no '+' is added when i = FCOUNT().

Any other ideas? I think it's a buffer overrun error which is crashing VFP.

TIA.

 

[baltman]

This is a simpler version. Perhaps it will work better for you.

Brian

cmd=&quot;&quot;
FOR x = 1 to AFIELDS(temparray)
IF temparray(x,2)=&quot;N&quot; &&Number
cmd=cmd+temparray(x,1)+&quot;+&quot;
ENDIF
ENDFOR
cmd=&quot;sum &quot;+LEFT(cmd,LEN(cmd)-1)
&cmd

 

[baltman]

Also, &quot;no '+' is added when i = FCOUNT()&quot; will only work for you if the last field in the table is a number... I think it is better/faster to use my methodology of always adding a &quot;+&quot; and then removing the last one.

Brian

 

[ramani]

HI

strx = ''
FOR i = 1 TO FCOUNT()
IF TYPE(FIELD(i)) = &quot;N&quot;
IF EMPTY(strx)
strx = 'SUM '+FIELD(i)
ELSE
strx = strx + '+'+FIELD(i)
ENDIF
ENDIF
ENDFOR
IF EMPTY(strx)
=MESSAGEBOX(&quot;No numeric fields to SUM&quot;,0+16)
ELSE
strx = strx + &quot; TO total&quot;
&strx
ENDIF



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[beesman]

Thanks Baltman & Ramani - both your solutions seem pretty good. However, using both solutions I still get the following error message before being dumped out of VFP:

Microsoft Visual C++ Runtime Library
Buffer overrun detected!
Program: ...\vfp8.exe
A buffer overrun has been detected which has corrupted the program's internal state. The program cannot safely continue execution & must now be terminated.

Any ideas what's wrong? Could it be my install of VFP?

Thanks again....

 

[baltman]

You may have a data issue within a particular field. Try the code below and see if any singular field invokes the error.

Alternatively, your total may be outside of VFP's range of numbers?

Maximum value of an integer = 2,147,483,647
Largest number = 10 ^ 308

FOR x = 1 to AFIELDS(temparray)
IF temparray(x,2)=&quot;N&quot; &&Number
fieldname=temparray(x,1)
?fieldname
sum(&fieldname) to y
?y
ENDIF
ENDFOR

 

[baltman]

Using this code, a sum of each individual numeric field is created in a cursor, and then the 'total' is calculated from the cursor. Perhaps it will side-step whatever issue you are having if there isn't a data problem within a specific field.

Brian

MyTable=&quot;DataTable&quot;

CLOSE ALL
cmd=&quot;&quot;
USE &mytable
FOR x = 1 to AFIELDS(temparray)
IF temparray(x,2)=&quot;N&quot; &&Number
cmd=cmd+&quot;, sum(&quot;+temparray(x,1)+&quot as &quot;+temparray(x,1)
ENDIF
ENDFOR
cmd = RIGHT(cmd,LEN(cmd)-1)
SELECT &cmd from &MyTable into cursor temp

total_val=0
FOR x=1 to AFIELDS(temparray)
VarField=(temparray(x,1))
total_val=total_val+&VarField
ENDFOR
?total_val

SELECT &MyTable
return

 

[beesman]

That's great Brian - I appreciate your help with this - but I need the SUM of each record, not the total SUM of each numeric field.

At the moment total_val produces the sum of the sum of all fields..if you get my drift

TIA

 

[baltman]

MyTable=&quot;TestData&quot;
MyKey=&quot;MyKey&quot;

CLOSE ALL
USE (MyTable)

IF VARTYPE(Num_Total)=&quot;U&quot; && field doesnt exist
ALTER TABLE (MyTable) ADD COLUMN Num_Total n(20,5) &&adjust as needed
ENDIF

strx = ''
FOR x = 1 TO AFIELDS(temparray)
IF temparray(x,2) = &quot;N&quot; AND temparray(x,1)#&quot;NUM_TOTAL&quot;
strx = strx + temparray(x,1)+ '+'
ENDIF
ENDFOR
strx=LEFT(strx,LEN(strx)-1)

SCAN &&so if you have a line where the total fails, you will know where it is
repl Num_Total with &strx
ENDSCAN

&&or

SELECT dist &mykey,&strx as NUM_TOTAL FROM &mytable INTO TABLE inividual_sums

 

[jimstarr]

The following code sums 1 field at a time. I doubt that it will solve the problem, but at least you may get a better look at where the crash is occurring.

total = 0
FOR i = 1 TO FCOUNT()
IF TYPE(FIELD(i)) = &quot;N&quot;
fldx = FIELD(i)
SUM &fldx TO totx
total = total + totx
ENDIF
ENDFOR

Jim

 

[beesman]

Thanks to everyone for all your help - baltman's SCAN...ENDSCAN command seems to do the trick perfectly