sum of fields

[fishman13]

I am trying to get a balance of invoices that are current, over 30 days, over 60 days and greater then 90 days. I am using four sum commands to temporary variables and them displaying these cariables in a label caption. The total sum is displayed below. My question(s) are is there a way to select these balance by only using one sum command and why does the variable and the amount get displayed on the bottom of the form.

select charge
sum tbalance ;
for charge.account == thisform.pageframe1.page1.txt_acct.value ;
to TOTBAL

 

[Mike Lewis]

Fishman,

To prevent the variable being displayed, try SET TALK OFF. If your form has a private data session, you will need to do this in the Load or Init if the form, or in the method containing the Sum.

Another approach you can take is:

SELECT SUM(TnBalance) AS TotalBal, SUM(another_field) AS AnotherBal FROM Charge WHERE charge.account == thisform.pageframe1.page1.txt_acct.value

Give it a try.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[ramani]

Hi Fishman,

1. A MIke said.. to set the display off ..
SET TALK OFF
you can add this line along with your SET commands or add it in the init if your form.

2. I was about to suggest SELECT statement.. which Mike has suggested but with a difference..

SELECT ;
NTOM(SUM(IIF(BETWEEN(DATE()-fieldDate,0,30),tbalance,0)) ;
AS tot30, ;
NTOM(SUM(IIF(BETWEEN(DATE()-fieldDate,31,60),tbalance,0)) ;
AS tot60, ;
NTOM(SUM(IIF(BETWEEN(DATE()-fieldDate,61,90),tbalance,0)) ;
AS tot90, ;
NTOM(SUM(IIF(DATE()-fieldDate>90),tbalance,0)) ;
AS tot91plus ;
FROM charge WHERE charge.account == ;
thisform.pageframe1.page1.txt_acct.value
INTO cursor myTotal

Now display myTotal.tot30 etc...





ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[fishman13]

Thanks

I have it working, but I have one small problem with the display of the fields. I am placing them onto a form for a summary using read only text boxes. However if the amount returned does not contain any change (100.00) I can only get the text box to show 100 . How can I prevent (or format) the text box to include the decimals. I tried the format property to $, but I needed the control source set to a numeric. I also tried an input mask but that did not change it. I am using VFP5.

 

[EzLogic]

input mask: 9999.99

Ali Koumaiha
TeknoSoft Inc
Farmington Hills, Michigan