Import query from access to excel, link to template, email on

XP Pro/Excel 2002/Microsoft Office 2003

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #],
[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)=[forms]![ParamControl#]![Ending
ControlNum]));

"Bill Manville" wrote in message
...
Can you post the SQL of the Access query?
And confirm the version of Office that you are using?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Quite a big query to redefine in MSQuery from scratch.
What I suggest you do is use Data / Import External Data / New Database
Query to define a simple query on the same database.

After returning results to Excel, right-click the results area and Edit
Query. If it doesn't take you direct to the MSQuery query grid, click
Next until you reach the end of the wizard and then select to edit the
query in MS Query.

In MS Query click the SQL button and then paste this slightly modified
version of your query into the SQL window replacing what was there
before.

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle,
Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order
Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order
Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%],
Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #],
[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER
JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID
WHERE Customers.ControlNum=[First Control Number?] And
Customers.ControlNum=[Last Control Number?]

If you now try to run the query by clicking the ! button it might flag
some syntax it doesn't like - in which case give me details and I'll
try to help you fix it - or it will prompt you for the 2 parameters
(First Control Number and Last Control Number).

File / Return results to Excel.

Then when you refresh the query in Excel it should prompt you again for
the parameters. As mentioned before you can link the parameters to
cells if you wish.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup