Query works until sort is added

[Phil Smith]

SELECT SellableItemsList.name, SellableItemsList.item_id, item.short_desc
FROM item INNER JOIN (SellableItemsList LEFT JOIN PriceListDistributor
ON SellableItemsList.item_id = PriceListDistributor.item_id) ON
item.item_id = SellableItemsList.item_id
WHERE (((PriceListDistributor.price) Is Null Or
(PriceListDistributor.price)=0))
ORDER BY SellableItemsList.name, item.short_desc;

Fails with "Invalid Operation"
The only difference in the following query is that item.short_desc is
not in the order by, and it works just fine.

Any Clues?


SELECT SellableItemsList.name, SellableItemsList.item_id, item.short_desc
FROM item INNER JOIN (SellableItemsList LEFT JOIN PriceListDistributor
ON SellableItemsList.item_id = PriceListDistributor.item_id) ON
item.item_id = SellableItemsList.item_id
WHERE (((PriceListDistributor.price) Is Null Or
(PriceListDistributor.price)=0))
ORDER BY SellableItemsList.name;

Phil

 

[Douglas J. Steele]

It could be because Name is a reserved word in Access.

If you cannot (or will not) rename the field, at least put square brackets
around it:

SELECT SellableItemsList.[name], SellableItemsList.item_id, item.short_desc
FROM item INNER JOIN (SellableItemsList LEFT JOIN PriceListDistributor
ON SellableItemsList.item_id = PriceListDistributor.item_id) ON
item.item_id = SellableItemsList.item_id
WHERE (((PriceListDistributor.price) Is Null Or
(PriceListDistributor.price)=0))
ORDER BY SellableItemsList.[name], item.short_desc;

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), check what Allen Browne
has at http://www.allenbrowne.com/AppIssueBadWord.html

 

[Phil Smith]

I can't change the names of the underlying fields, (ODBC to an
accounting application I did not write,) but I can and usually do alias
..name, because there are about ten tables that use that field name.
However, this has never been an issue in the past.

Also, making the change you suggested, (as well as aliasing that field,
makes no difference. Same error.

 

[Phil Smith]

Figured it out, sorta. The query is baed in part on another query,
SellableItemsList. Both that query, and the original query, reference
Item and Item_Type. I removed those from the underlying query, and was
able to sort fine.

Thanx

I can't change the names of the underlying fields, (ODBC to an
accounting application I did not write,) but I can and usually do alias
.name, because there are about ten tables that use that field name.
However, this has never been an issue in the past.

Also, making the change you suggested, (as well as aliasing that field,
makes no difference. Same error.

It could be because Name is a reserved word in Access.

If you cannot (or will not) rename the field, at least put square
brackets around it:

SELECT SellableItemsList.[name], SellableItemsList.item_id,
item.short_desc
FROM item INNER JOIN (SellableItemsList LEFT JOIN PriceListDistributor
ON SellableItemsList.item_id = PriceListDistributor.item_id) ON
item.item_id = SellableItemsList.item_id
WHERE (((PriceListDistributor.price) Is Null Or
(PriceListDistributor.price)=0))
ORDER BY SellableItemsList.[name], item.short_desc;

For a comprehensive list of names to avoid (as well as a link to a
free utility to check your application for compliance), check what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html