Bottom Records

[Charles Hoens]

I have a checkbook dbf which is sorted by paid date. In order to print the last 65 records of a particular month, I set up a global variable called enddate. I then use the following expression in order to get the last 65 records to print them.

rep_filter = "bottom_records(65,\"\") .and. cdate(paiddate)<=cdate(enddate)"

The output I am getting though is not the last 65 records that meet the filter criteria, but the last 65 records of the checkbook database. Blanks are printed if the filter criteria is not met.

For example, if enddate={03/31/09} and the last 5 records in the checkbook database have a paiddate of {04/01/09}, then I would only get 60 records printing.

The question is how can I get the last 65 records for the filter requested?

Thanks,

Charlie

[Stan Mathews]

Re: Bottom Records

rep_filter = "bottom_records(65,cdate(paiddate)<=cdate(enddate)) "

I think will work.

Or not.

The question is how can I get the last 65 records for the filter requested?

What 65 records do you want? The last 65 in enddate order, paiddate order?

Always easier to check these things with a sample database than to try to imagine what will happen.

[Charles Hoens]

Re: Bottom Records

Thanks Stan. Enddate is simply the global variable of a date where the last record should be. The dbf is sorted in paiddate order, so at any time I want to plug in a variable enddate and get the 65 records that precede that date. What I am getting now is just the last 65 records in the dbf.

I'll try your expression.

Charlie

[Charles Hoens]

Re: Bottom Records

Stan,

Sorry, your expression did not work and I got an invalid report filter error.

I even tried filtering the dbf prior to issuing the bottom_record request, but that didn't help (and logically I can understand that).

Any other ideas?

Charlie

[G Gabriel]

Re: Bottom Records

rep_filter = "bottom_records(65,\" paiddate<=var->enddate\")"

[Stan Mathews]

Re: Bottom Records

Had to go read up on this process.

The term "bottom" does not imply that record values are examined. "Bottom" means last, given the current sort order.

So I think you need a button on a form, based on the table, to query the table and then base the report on current selection of records..

Code for button...

dim tbl as P
dim inx as P
tbl = table.current()
query.filter = "bottom_records(65,\"cdate(paiddate)<=cdate(enddate)\") "
query.order = "paiddate"
inx = tbl.query_create()
topparent.resynch()
report.print("yourreportname")

[Charles Hoens]

Re: Bottom Records

Gabriel: This change makes no difference in the code.

Stan: I tried that during one of my iterations to come up with a solution. The only other thing that I was thinking about was possibly creating a new index in the button code based on the variable input of enddate. Then, like the counting of records, it might work. I mention counting of records only because, as you know, a record count counts all records in the database even if it is filtered. The only way to get a proper record count is to create an index and then count the records that match that index.

Another, (more painful) way, would be to copy the records in question to another temp dbf and then run the report from there.

I'll try those ideas and get back to you. In the meantime, if you think of something more elegant, let me know.

Charlie

[G Gabriel]

Re: Bottom Records

The last syntax is the correct syntax. The problem I believe is in how you are using: rep_filter

[Charles Hoens]

Re: Bottom Records

Gabriel,

Nice picture of you. I hadn't seen that before!

Did George Bernard Shaw actually say that? I didn't know he used Sham Wows!

Charlie