Hey guys,

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:



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?

Hi

One option would be to loop through your recordset to create the string as you suggested, then use a temporary table to store each of the concatenated strings as a single record. Once all of the records are stored you can use the TransferSpreadsheet method to export to the csv file

Great idea - i never thought of it that way.

I'll have a look into that now. Thanks

- hope it works out for you

You can use the query object if you want ... check out ExportToCSV()