Exclusion on selected record

[Nro]

Hi. I have one very simple question for data selecting.

I have two tables, tblCust and tblInvoice. The link between the two tables is the customer code (tblCust.cst_Code = tblInvoice.inv_CstCode).

In tblInvoice, I have one field inv_Date, witch is the invoicing date.

Now, I want to select all the customers who do not have an invoice for a certain period. I understand left and right join, but I can’t figure out how to do that. If I put a criteria like this;

{ tblInvoice. inv_Date } in DateTime (1996, 01, 08, 00, 00, 00) to DateTime (1996, 01, 31, 00, 00, 00)

I’d like to find all the customers who don’t have an invoice in this date interval. Maybe it’s possible with “Add command”.

I’m using Crystal 10 and SQL Server 2000

Thanks in advance.

Nro

 

[lbass]

If you use a left join FROM the Customer table to the Invoice table, and use no record selection, then you can create a formula:

//{@inperiod}:
if {tblInvoice. inv_Date } in DateTime (1996, 01, 08, 00, 00, 00) to DateTime (1996, 01, 31, 00, 00, 00) then 1

Insert a group on {table.customer} and then go to report->selection formula->GROUP and enter:

sum({@inperiod},{table.customer}) = 0

-LB