Export Spreadsheet from Switchboard

[Guest]

Hands down. You are the pro!
Thanks Ken

I must change my answer. There is a way for you to export the data without
the SortOrder field being exported as well.

Instead of exporting the UNION query directly, save it into your database
and give it a name (such as qryUnion). Now create a new query with this SQL
statement:

SELECT qryUnion.EnterDate, qryUnion.VendorID, qryUnion.VendorName,
qryUnion.InvoiceID, qryUnion.VendorCtry, qryUnion.VendorCostCenter,
qryUnion.PerDate, qryUnion.InvDesc, qryUnion.InvAmt,
qryUnion.InvoiceCode
FROM qryUnion
ORDER BY qryUnion.SortOrder;

Then export the above query.
--

Ken Snell
<MS ACCESS MVP>

Any book on SQL should be helpful in learning how to write queries. One
good book I've used often is "SQL Queries for Mere Mortals" by Michael
Hernandez and John Viescas. (A new Second Edition by John Viescas will be
published later this year.)

There is no way to hide the SortOrder column through the export process
itself. You'd need to use EXCEL Automation after the file is created to
open the file and delete that column from the EXCEL file's spreadsheet.
--

Ken Snell
<MS ACCESS MVP>

Hello Ken,
Where can i learn more on working and creating these type of query's"?
The
export to excel works great and i would like to hide the column titled
SortOrder. Sorry for late response, discussion boards technical problems
occured.


:

You can add more criteria to the query:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*") AND
((VendorInv.InvDesc) Like "AUDIT" & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*")) AND
((TT.InvDesc) Like "AUDIT" & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

 

[Ken Snell \(MVP\)]

Hands down. You are the pro!
Thanks Ken

You're welcome!