I am trying to create a CSV file with the following format (for import into MYOB AccountRight financial software):
InvoiceNum1,Line1,... InvoiceNum1,Line2,... InvoiceNum2,Line1,... InvoiceNum2,Line2,... InvoiceNum3,Line1,... ....
So basically each invoice is separated by a blank line, and there can be one or more invoice items per invoice. I currently have a query written that does the following format (i.e. without blank lines):
InvoiceNum1,Line1,... InvoiceNum1,Line2,... InvoiceNum2,Line1,... InvoiceNum2,Line2,... InvoiceNum3,Line1,...
This query is a union query that brings together two normal sub-queries.
Unfortunately the software I am importing into is extremely stupid and treats this all as one invoice. So I need a way to insert the blank lines between each invoice. Before I export the CSV I am applying filters to the query (so I can select a date range) which means that any blank lines I manage to insert in my query get filtered out because they don't contain a date.
The solution I am currently planning is to write a separate program that the user can use to format the CSV file correctly before importing it into MYOB. This is obviously a huge hack and I would prefer a native Access solution if possible.
I'm using Access 2007, but can upgrade/downgrade to a different version if it gets me the functionality I need.