We have a client on Navision Adv. Distr 2.01B. They are trying to use the C/ODBC driver and Excel to sum the quantity field in the item ledger. Here is the sql statement The query works up to the point that I add the sum to the quantity field. SELECT “Item Ledger Entry”.“Item No_”, Sum(“Item Ledger Entry”.Quantity) AS ‘Sum of Quantity’ FROM Item Item, “Item Ledger Entry” “Item Ledger Entry” WHERE Item.No_ = “Item Ledger Entry”.“Item No_” AND ((Item.“VOC Content”=1) AND (“Item Ledger Entry”.“Posting Date” Between {d ‘2001-01-01’} And {d ‘2002-06-12’}) AND (“Item Ledger Entry”.“Entry Type” In (‘Negative Adjmt.’,‘Positive Adjmt.’,‘Transfer’))) GROUP BY “Item Ledger Entry”.“Item No_” The error message they get is:“Expected lexical element not found: identifier>.” Based on other forum topics I had them unintall the odbc driver, reinstall it and configure the options. In field “Identifiers” they selected “All Characters” instead of the default “all except dots”. Does anyone have any suggestions? Thanks for your help, Lori Helwig
Hi, Not sure if this will help but I am pretty sure that the excel does not like full stops in field names… In the past i’ve always chosen the ODBC set up option that replaces the full stop with an underscore and modified my query appropriately. This seems to work fine. Regards Simon