I am trying to create a view in MS ACCESS and am getting the following error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntax error in field definition.
Here is the create statement. I've tested the select portion separatey and
that works.
CREATE TABLE vieInvoices
(
CustomerKey, CustomerName, Invoice, InvoiceNumber, PeachtreeInvoiceNumber,
InvoiceDate, ShipDate, ShippingMethod, ShipToAddressKey,
ShipToName, ShipToAddr1, ShipToAddr2, ShipToCity, ShipToState, ShipToZip,
ShipToCountry, OrderNumber, ShippingCharge, SalesTax, Comment,
EnteredByKey, EnteredByName, ChangedByKey, ChangedByName, ChangedOn,Line,
Quantity, Key, ID, Description, TypeKey, TypeName, Price
)
AS
select tabOrders.Customers_FK , vieCustomerList.CustomerName,
IIf([PeachtreeInvoiceNumber] Is
Null,[tabInvoices].[InvoiceNumber],[PeachtreeInvoiceNumber]),
tabInvoices.InvoiceNumber, tabInvoices.PeachtreeInvoiceNumber,
tabInvoices.InvoiceDate, tabInvoices.ShipDate, tabInvoices.ShippingMethod,
tabOrders.ShipToAddressKey, tabOrders.ShipToName, tabOrders.ShipToAddr1,
tabOrders.ShiptoAddr2, tabOrders.ShipToCity, tabOrders.ShipToState,
tabOrders.ShipToZip, tabOrders.ShipToCountry, tabInvoices.OrderNumber,
tabInvoices.ShippingCharge, tabInvoices.SalesTax, tabInvoices.Comment,
tabInvoices.EnteredBy_FK, tabPeople.Name, tabInvoices.ChangedBy_FK,
tabPeople_1.Name, tabInvoices.ChangedOn, tabInvoicedItems.Line,
tabInvoicedItems.Quantity, tabInvoicedItems.Item_FK, tabInvoicedItems.ID,
tabInvoicedItems.Description, IIf([ItemType_FK] Is Null,55,[ItemType_FK]),
IIf([ItemType_FK] Is Null,'Other',[tabitemTypes].[TypeName]),
tabInvoicedItems.Price
FROM (tabPeople AS tabPeople_1 RIGHT JOIN (((tabInvoices LEFT JOIN tabPeople
ON tabInvoices.EnteredBy_FK = tabPeople.Person_PK) INNER JOIN tabOrders
ON tabInvoices.OrderNumber = tabOrders.OrderNumber) INNER JOIN
vieCustomerList ON tabOrders.Customers_FK = vieCustomerList.CustomerKey)
ON tabPeople_1.Person_PK = tabInvoices.ChangedBy_FK) LEFT JOIN
(((tabInvoicedItems LEFT JOIN tabOrderedItems
ON (tabInvoicedItems.Line = tabOrderedItems.Line) AND
(tabInvoicedItems.OrderNumber = tabOrderedItems.OrderNumber)) LEFT JOIN
tabItems
ON tabInvoicedItems.Item_FK = tabItems.Item_PK) LEFT JOIN tabItemTypes ON
tabItems.ItemType_FK = tabItemTypes.ItemType_PK)
ON tabInvoices.InvoiceNumber = tabInvoicedItems.InvoiceNumber;
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
- Re: Create View problem Andy Ousterhout
- Re: Create View problem Jochem van Dieten
- RE: Create View problem Andy Ousterhout
- Re: Create View problem kpeterson