Two typical ways of doing this:
- create a relationship to the COMaster table using a global date on the left and the approved date on the right side and use the right comparison operator (<=, >=).
- use the ExecuteSQL() function with the right WHERE clause and sum the results.
Wim's suggestion of ExecuteSQL is definitly a good method. I just did a report that had to come up with sums from a number of different tables and ExecuteSQL worked very well. Seedcode's SQL Explorer and Beverly Voth's ExecuteSQL the missing manual are very helpful in getting this type of thing started.
Guys, thanks for the excellent suggestion, ExecutSQL worked perfectly, and Seedcode's SQLExplorer definitly made it easy to learn. On question though, it only returns the data type of text. I tried using GetAsNumber(SQLReesult) and using field formatting to format the result as a currancy, but the GetAsNumber seems to concantinate the 2 values into one number. so instead of having
I end up with
I need the final output to bwe $50,000.00
How can I work around this? do I parce the result list, then GetAsNumber each value, then put them back in a list()?
I am so close and again appricate everyone;s help.
areyou using Select Sum() or are you trying ot sum them in a calc first?
I would probably loop through the result and apply the format, rather than try to complicate the ExecuteSQL query itself.
Thanks for everyone's help. I endedup using a SUM in the SQL statement to get just the filtered total I needed, then used GetAsNumber() to format it. I had to write a sperate ExecuteSQL() for each total, but no big deal.