Exporting to txt with data split by condition

[djr2023]

Hi, I'm attempting something that may not be possible.

I have my final summary table created, and I want export that to a txt file (can do) but split by country using a key in the table.

I.e. SUMMARY_UK.txt contains all data from the Summary table with the UK country ID.
SUMMARY_FR.txt contains all the data with France country ID, and so on.

Is this something I can do, or do I need to use split it in my queries? Where would I start?

Thanks in advance.

 

[theDBguy]

Hi. Welcome to AWF!

Unfortunately, Split() doesn't work in queries. You can probably use some VBA instead.

 

[djr2023]

VBA was what I was thinking, I'll have a look into split() thanks

 

[theDBguy]

VBA was what I was thinking, I'll have a look into split() thanks

Not sure exactly what you're trying to do but take a look at this simple function in case it helps.

Code Snippets

 

[Gasman]

I would have thought you would just be running a query with a parameter of CountryID and output to a text file of an appropriate name, for as many countries as you have in the table?

 

[Pat Hartman]

This can be a little tricky so to expand a little, you need a query or table that contains a list of all the countries you wish to export. You need a form with a button to run the report as well as an unbound, hidden textbox. If it is more convenient to use a Tempvar than a control on a form, that is OK also. The RecordSource query of the report must get its criteria from either referencing the hidden, unbound control on the form or by referencing the TempVar.

You then create a VBA loop that reads the country table/query and for each row in that table, you need to export a PDF. Inside the loop, you first copy the CountryID to the hidden, unbound control or the TempVar. Then you format the name of the .PDF referencing the hidden, unbound control or the TempVar. Then you output the .pdf.