I want only to include certain records. 1

[scottpdann]

In a membership organization, we ran a special from January 20th and later. For those who renewed. I want to find out their prior transaction date. I can only get the report to include either just the Current transactions, or a 1000+ page report including all of the transactions. I want to scope out that 1000+ page group to include only members who have transcations after 1/20/04.

Please help.

 

[bdreed35]

Try this:

From the Report menu, choose Edit Selection Formula, Record.
Enter your criteria in the editor like this:

{table.trans_date} >= CDate(2001,01,20)

~Brian

 

[synapsevampire]

To build the report out so that you can enter dates, create a parameter 1st, as in:

Insert->Field Object->Right click parameters and select New->Name it->Select a Date type

Now use that in your record selection formula and the report will always prompt for the date to limit rows by.

Note that you can make it prompt for a start and an end by changing it to a Range type of parameter.

The record selection would then look like:

{table.trans_date} = {?MyDateParm}

Sunstituting your date field for {table.trans_date} and your date parameter for {?MyDateParm}

-k

 

[scottpdann]

The Cdate thing didn't change my problem. I still need to get the older records of the members who fit the After January 20th criteria.

 

[scottpdann]

I don't think the parameter thing will help. I want to keep the older records of the members who fit the criteria of later than January 20th.

Can someone translate this thought into a formula.

If Member has a transaction date > than 1/20/04, then include ALL of his records.

Thanks for your responses.

 

[lbass]

You could do this by not using a record select on the date field. Group on the {table.memberID} and then go to report->edit selection formula->GROUP and enter:

maximum({table.date},{table.memberID}) >= date(2004, 01, 20)

This will display all records for those members whose most recent date is on or after 1/20/2004. The down side to this is that the report is still returning all records for everyone, although displaying only the ones you want.

There might be a SQL approach that would return fewer records, but someone else would need to help with that.

-LB

 

[scottpdann]

That worked lbass, thank you!

Now, is there a way to only show the next most recent date before January 20th rather than every date for each member?

 

[lbass]

Create a formula {@date<120}:

if {table.date} < Date(2004, 01, 20) then {table.date}

Insert a maximum on this formula and drag it into the group header. Then you can suppress the details. If you want to show other details related to this date, then instead, go to format->section->details->suppress->x+2 and enter:

{table.date} <> maximum({@date<120},{table.memberID})

-LB

 

[synapsevampire]

<chuckling>

&quot;I want to find out their prior transaction date. I can only get the report to include either just the Current transactions&quot; translated to &quot;If Member has a transaction date > than 1/20/04, then include ALL of his records.&quot;

I need to take a break from Tek-Tips I fear...

-k

 

[scottpdann]

I did a group footer for each member with the maximum and the TopN using 2 then subtracted the date. It worked.

Thanks all for your help.