Richard Horne
Member
- Local time
- Today, 01:58
- Joined
- Oct 15, 2020
- Messages
- 55
I'm using the TransferSpreadsheet function to output a query to a spreadsheet. The output works great except the ordering of the columns isn't matching the order specified in the saved query. Does anyone know of a way to maintain the correct order?
To give you an example, I expect a query to be ordered 1,2,3,4,5,6,7,8,9 whereas the outputted spreadsheed is something like 1,2,3,8,5,7,6,9,4.
To give you an example, I expect a query to be ordered 1,2,3,4,5,6,7,8,9 whereas the outputted spreadsheed is something like 1,2,3,8,5,7,6,9,4.
Code:
'Build our query
str_sql = "SELECT tbl_purchase_orders.purchase_order_id AS PO, tbl_purchase_orders.sales_order_ref AS SO, tbl_orders.customers_ref AS [Cust Ref], tbl_companies.company_name AS Supplier, " _
& " tbl_purchase_orders.order_date AS [Date], tbl_purchase_order_details.delivery_required_by AS [Due Date], tbl_purchase_order_details.item_number AS [Line Item], " _
& " tbl_purchase_order_details.customer_item_reference AS [Item No], tbl_purchase_order_details.product_code AS [Product Code], tbl_products.tl_number AS [TL Number], " _
& " tbl_item_types.item_type_name AS [Item Type], tbl_secondary_item_type.secondary_item_type_name AS [Secondary Type], tbl_products.design AS Design, tbl_products.sort_colour AS Colour, " _
& " Nz([price_sterling],0) AS Price, tbl_purchase_order_details.quantity AS Qty, Nz([SumOfqty_approved],0) AS Appr, [quantity]-[Appr] AS Rem, [rem]*[price] AS [Value]" _
& " FROM tbl_orders INNER JOIN (purchase_orders_marked_off_totalled RIGHT JOIN (tbl_staff INNER JOIN (((((tbl_purchase_orders INNER JOIN tbl_purchase_order_details ON " _
& " tbl_purchase_orders.purchase_order_id = tbl_purchase_order_details.purchase_order_id) INNER JOIN tbl_companies ON tbl_purchase_orders.company_id = tbl_companies.company_id) " _
& " INNER JOIN tbl_products ON tbl_purchase_order_details.product_code = tbl_products.product_code) INNER JOIN tbl_item_types ON tbl_products.item_type_id = tbl_item_types.item_type_id) " _
& " INNER JOIN tbl_secondary_item_type ON tbl_products.secondary_item_type_id = tbl_secondary_item_type.secondary_item_type_id) ON tbl_staff.staff_id = tbl_purchase_orders.ccp_id) " _
& " ON purchase_orders_marked_off_totalled.item_id = tbl_purchase_order_details.detail_id) ON tbl_orders.sales_order_id = tbl_purchase_orders.sales_order_ref " _
& " WHERE (((tbl_purchase_orders.complete) = 0) And ((tbl_purchase_order_details.item_complete) = 0) And ((tbl_purchase_orders.purchase_order_cancelled) = 0) " _
& " AND (tbl_purchase_orders.ccp_id = " & dbl_current_employee & "))" _
& " ORDER BY tbl_purchase_orders.purchase_order_id, tbl_purchase_order_details.item_number;"
'Save the query to our temp saved query
CurrentDb.QueryDefs("purchase_orders_live_temp").SQL = str_sql
'Export query to spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "purchase_orders_live_temp", "C:\DEAN\Exported Files\live_pos.xlsx", True
Last edited: