Excluding records from a query

[lawtonl]

I have a query with 3 tables MDD, Contacts and SettoDisc. The first 2 table have all the info I want to pull from the query and the third table has a field in it called [MPN] which matches the field [extref] in MDD.

I want to exclude any records from the query where [ExtRef] matches [MPN] and I'm struggling to do this, I thought it would work out be quite simple but that hasn't been the case for me.

I can pull records out where both field match but thats the opposite of what I want.

The code I'm using to try and exclude records where the fields match is:

Not "[mpn]"

That is in the [ExtRef] criteria.

Both fields are text format too.

Any help is appreciated

Thanks

 

[Mr. B]

Try creating a Select query that will return a list of value from the the [MPN] field in the third table using any criteria you need. If all you want is the simple list then you would not need any criteria. If you are using the QBE to develope the query, switch to the SQL view and copy the sql statement and use this Select sql statement as the criteria for the [extref] field usin and "IN" clause only make it Not In().

Your criteria for the [extref] field would be:
Not In(Paste the Complete Select Sql Statement Here)

 

[lawtonl]

Thanks

That worked great