CODBC Woes / Queries

brucem

Hi there,

I am trying to convert some Ms-Access queries to Pass through queries that the navision server will run. Can anybody help me with the syntax for converting joins LEFT OUTER INNER etc.

I seem only to be able to create pass thrus with equals

For example the code below returns 43 records ( all equals)

SELECT Item.ProdinfoNo, Item.No_, Item.Description, Item_Ledger_Entry.Posting_Date, Item_Ledger_Entry.Entry_Type, Item_Ledger_Entry.Source_No_, Item_Ledger_Entry.Document_No_, Item_Ledger_Entry.Quantity, Item_Ledger_Entry.Source_Code, Item_Ledger_Entry.Lot_No_, Item_Ledger_Entry.No__of_Packs, Item_Ledger_Entry.Remaining_Quantity,Sales_Shipment_Header.Your_Reference, Sales_Shipment_Header.Order_No_

FROM Item, Item_Ledger_Entry, Sales_Shipment_Header

WHERE Item.No_ = Item_Ledger_Entry.Item_No_ AND Item_Ledger_Entry.Document_No_= Sales_Shipment_Header.No_ AND Item.ProdinfoNo=41


BUT the code below with the correct join ( from Access ) returns 123 records

SELECT Item.ProdinfoNo, Item.No_, Item.Description, Item_Ledger_Entry.Posting_Date, Item_Ledger_Entry.Entry_Type, Item_Ledger_Entry.Source_No_, Item_Ledger_Entry.Document_No_, Item_Ledger_Entry.Quantity, Item_Ledger_Entry.Source_Code, Item_Ledger_Entry.Lot_No_, Item_Ledger_Entry.No__of_Packs, Item_Ledger_Entry.Remaining_Quantity, Sales_Shipment_Header.Your_Reference, Sales_Shipment_Header.Order_No_

FROM (Item INNER JOIN Item_Ledger_Entry ON Item.No_ = Item_Ledger_Entry.Item_No_) LEFT JOIN Sales_Shipment_Header ON Item_Ledger_Entry.Document_No_ = Sales_Shipment_Header.No_

WHERE (((Item.ProdinfoNo)=[Forms]![NavItemsMovesForm]![product]));

the difference is the JOINS !!!

Does CODBC on standard Navision support this ??

Bruce