Troubleshooting - Cannot use alias in MS Query
I am getting the below error message when trying to SELECT data fields AS Alias, the statement runs fine otherwise.
[sql syntax error] Expected lexical element not found:=
Please see the following SQL statement:
SELECT Item.Name AS SKU, Item.CustomFieldColor AS Item, Item.Description, Item.SalesPrice, Item.PurchaseCost, Item.QuantityOnHand FROM Item Item WHERE (Item.Name<>'IFR' And Item.Name<>'OTW') AND (Item.Description<>'') AND (Item.Type='ItemInventory') ORDER BY Item.Name
The above statement is working fine in VB Demo, MS Access. But I am facing the issue in MS Excel.
MS Excel has some issue when you alias in the query. When you try to use Microsoft Query to return data from some third-party databases into Microsoft Excel, apostrophes (') around alias names can cause the query to fail.
Please refer below mentioned link to resolve this issue:
Using a field alias in Query does not work with some third-party databases
You can either apply hotfix or change registry values.
The result after changing registry values & execute the query again: