IsNull and a Date Parameter 1

[yunvme]

Hi,
I have two tables (table A and table B). table A contains cusotmer information and table B contains customer activity information for the past 5 years.
I have joined the two tables using left join as {tablea.customer_id}={tableb.customer_id} in both tables.
what i'm trying to see if there are customers in table A that are not in table B within a date range. i used the isnull{tableb.cusotmer_id} in the record selection which is okay but if a cusotmer has had activity at any time they do not show in the report.
is there a way to use isnull{tableb.customer_id} and {?date range}. when i add in the date range parameter to the record selection my results are blank.

here is a sample of the tables as well as the results I am looking for:
tableA
Number name
1 Bob
2 Joe
3 Mary
4 Sam
5 David
6 George

tableB
number code date
1 A 3/30/2010
2 B 4/10/2010
3 A 4/20/2010
4 C 2/15/2010
5 B 4/16/2010

Results for no activity from 4/1/2010 to 4/30/2010
number name
1 Bob
4 Sam
6 George

thanks in advance

 

[lbass]

Keep the left join, but don't select on the date range. Instead, create a formula in the field explorer:

//{@inrange}:
if isnull({table.date}) or
{table.date} = date(0,0,0) or
not({table.date} in {?date range}) then
0 else 1

Insert a group on {tablea.customerID}, and then go to report->selection formula->GROUP and enter:

sum({@inrange},{tablea.customerID}) = 0

-LB

 

[yunvme]

LB...If you haven't been told today....YOU ROCK! ;-)

it worked like a charm!

thanks again!