I have an order tracking database that our sales office use to allocate delivery dates to orders. The transport office have a piece of Route Planning software that, given the information in the correct format, will determine the optimal delivery route based on distance, available resources, etc. The Route Planner needs a csv file containing all orders to be delivered on a given date, with each order on its own line within the file. The lines in the csv file need to be in the following format:
[Customer]|[Delivery Town]||||[Delivery Postcode]|[Order Weight]|||[Delivery/Collection]|[Day 1/Day 2]|[Time At Drop]|[Earliest Drop]|[Latest Drop]|||||||||[Invoice Number]|[Job Number]|[Customer Reference]|||||
My query returns all the of the fields the import needs and in the correct order, and I can use the TransferText function to export the results of the query into a csv file, but I need to somehow manipulate the results of the query to include the pipe delimiter characters as above.
I know how to read the results of the query into a recordset and using VBA I can loop through each record in the recordset and create a string from the fields as required, but I don't know how to write the individual strings into a csv file?