Equivalent of "NOT EXISTS" in Recordselection formula

[samson123]

I am using CR XI, Access 2000

What is the equivalent of "NOT EXISTS" in the record selection formula ?

I have a queryas follows:
SELECT *
FROM TableA where TableA.custid = 100002 and not exists (Select * from TableB, TableA where TableB.custid =TableA.custid)


I would like to do this using record selection formu

Any Help will be appreciated.

 

[paulmarr]

Sounds like you want to do a left outjoin from Table A to Table B.

Cheers
paulmarr

 

[Madawc]

As well as left-outer - which says that the link is not compulsory - you need to check for the Table B entry not existing. Isnull(TableB.custid).

This works only when you aren't doing any other selections on Table B. At least that applies for Crystal 10.

Madawc Williams (East Anglia, UK)

 

[samson123]

As per your approach, In my Recordselection formula this is what i have written

if isnull({TableB.Custid}) then

{TableA.Custid} = 100002 and
{TableA.Custid} ={TableB.custid}

It does not retrieve any records...

What am I missing ?

 

[Madawc]

Table A and Table B should be linked using Database > Database Expert. Look at the SQL and you'll find the link is already there. You do not need to add it in record selection.

The statments also should not be linked by 'then'; I'm surprised it wasn't rejected by the syntax checking.

Try
if isnull({TableB.Custid})
and
{TableA.Custid} = 100002

Madawc Williams (East Anglia, UK)

 

[samson123]

madawc,

I copied the exact stuff that you mentioned and I was getting this message

"The keyword 'then' is missing".
By the way I forgot to mention to you there are two more tables Table C and Table D.

TableC also has the CustID and TableD also has CustID. There are linked to all the CustID in the Database expert.

Well if this not clear, let me tell you what I am trying to achieve..

Say Table A has 10 records. Table B could have have 5 records or 0 records.


TAbleC and Table D are master tables

If TableB has O records, I want to display all the 10 records from TableA.
else
I display 5 records from TableB

 

[Madawc]

Sorry, I ought to have said

isnull({TableB.Custid}) 
and
{TableA.Custid} = 100002

if.. then.. is for setting a value in a formula field. It doesn't belong in record selection.



Madawc Williams (East Anglia, UK)