Help with Query using the <> operator

[Paul7905]

I need some help with an access Query.

I have a table containing data pertaining to client orders.

The table contains a unique key (autonumber field) called "RsvNumber".

I want to create a query that select all records from the table except one record.

I wrote a query that extracts the "RsvNumber" key that I want to exclude. The name of this query is "CustomerReserveOrderExclude". When I run this query it returns one record with the field "RsvNumber" (in my test case, the number is "6".


I then created a select query containing the Order Table and the "CustomerReserveOrderExclude" query.

I joined the table and the query on the "RsvNumber" field (left join on order table (include all records from order table and only the records from the exclude query).

I then put in the criteria field for the Order table the following statement <>[CustomerReserveOrderExclude].[RsvNumber].

My test order table contains three records with the RsvNumber keys of 6, 7, and 8.

When I run the query, no records are returned.

If i remove the criteria statement, all records are returned from the order table.

if i change the criteria to =[CustomerReserveOrderExclude].[RsvNumber] I get only the record from the order table that matches the exclude query record (6)

if i change the criteria to: <> 6 then i get the 7 and 8 records (which is what i want).

if i change the criter to &quot;Like [CustomerReserveOrderExclude].[RsvNumber]&quot; i get the record with the RsvNumber (6)

Can anyone help me understand why the criteria: <> [CustomerReserveOrderExclude].[RsvNumber] does not work ??

The following is the SQL from the query:


SELECT ClientRsvOrderTable.RsvNumber, ClientRsvOrderTable.ClientID, ClientRsvOrderTable.RsvStatus, ClientRsvOrderTable.[RsvIsFarmRsv?], ClientRsvOrderTable.RsvDate
FROM ClientRsvOrderTable LEFT JOIN CustomerReserveOrderExclude ON ClientRsvOrderTable.RsvNumber = CustomerReserveOrderExclude.RsvNumber
WHERE (([ClientRsvOrderTable].[RsvNumber]<>[CustomerReserveOrderExclude].[RsvNumber]));

 

[tunsarod]

Paul,

Try using the word NOT instead of <>.

Regards
Rod

 

[llamaman]

Actually,
Correct VB syntax is the <> operator, but correct SQL syntax is the != operator. Use the != operator and you should be just fine.
Llamaman

 

[tunsarod]

Hi llamaman,

Read your note regarding != but it doesn't seem to work with Access97.

SAMS Teach Yourself SQL says:

&quot;Another option to <> is !=. Many of the major implementations have adopted != to represent not-equal. Check your particular implementation for the usage.&quot;

Is this a valid opera6tor in Access2000 perhaps?

Regards
Rod

 

[tunsarod]

Hi llamaman,

Read your note regarding != but it doesn't seem to work with Access97.

SAMS Teach Yourself SQL says:

&quot;Another option to <> is !=. Many of the major implementations have adopted != to represent not-equal. Check your particular implementation for the usage.&quot;

Is this a valid operator in Access2000 perhaps?

Regards
Rod

 

[demoman]

Try this - if it does not work, you have something queer going on.


SELECT ClientRsvOrderTable.RsvNumber, ClientRsvOrderTable.ClientID, ClientRsvOrderTable.RsvStatus, ClientRsvOrderTable.[RsvIsFarmRsv?], ClientRsvOrderTable.RsvDate
FROM ClientRsvOrderTable where ClientRsvOrderTable.RsvNumber not in (select CustomerReserveOrderExclude.RsvNumber from CustomerReserveOrderExclude)

 

[AnalystDBA]

Hi Paul,

I agree with Demonman.
Nested Select is the way to go.

Cheers