View Full Version : Tablesum help needed - filter problems??


Jim Faliveno
01-07-2004, 01:10 AM
Perhaps someone can help with this.

Goal: To sum the sales amounts in a table that occur after a certain date and place the result into a calculated field on a form.

This works: tablesum("Trans-2003.dbf", ".T.", "Sec_Dev_Price")

This does not: tablesum("Transaction.dbf", "Date_shipped=>'01/01/2003'", "Sec_Dev_Price")

The obvious problem is with the filter. Can this be done or am I expecting too much of A5?

Tom Cone Jr
01-07-2004, 01:32 AM


tablesum("Trans-2003.dbf", "dtos(Date_shipped)>= '20030101' ", "Sec_Dev_Price")

-- tom

Jim Faliveno
01-07-2004, 02:01 AM

Thank you. That works! It gives the sum of all sales after 01/01/2003.

How would one modify it to summarize the values between a range of dates for example between 02/01/2003 and 03/01/2003?

You keep crazy hours like I do!


Tom Cone Jr
01-07-2004, 02:36 AM

Actually, it should give the sum of all sales ON or AFTER 1/1/2003.

For a range of dates your filter expression could:

1) use the Between() function; or

2) use the .AND. operator to connect to logical expressions so that the qualifying date field value must be greater than or equal to a given date AND less than or equal to a second given date.

Why not try your hand with these and then let us know if you hit snags?

-- tom

Jim Faliveno
01-07-2004, 05:25 AM
Thanks. Will do!

Jim Faliveno
01-07-2004, 07:13 AM

I got this to work: tablesum("transaction.dbf",between_date("Date_Shipped",{01/01/2003},{12/31/2003}),"Sec_Dev_Price")

Thanks for your suggestions.

Now I'm woking on this: tablesum("transaction.dbf",between_date("Date_Shipped",StartDate,EndDate),"Sec_Dev_Price")

A5 has been crashing a lot on my Win XP Pro machine all through this effort. It crashes right after I put the calculated field on a form and then rclick to set the properties of the field. First the properties form comes up OK, then after I change the justification and go to set the number of characters and format of the field, the properties TAB dialog goes blank. Exiting the form without saving, then the screen goes white and I have to Ctl-Alt-Del to get out! Is this to be expected?