Export as .txt file w/out dashes

[Sonny Jim]

I am trying to export data from a query as a .txt file with Windows Encoding but my output always includes dashes that separate each record. Our EDI clients insist that this intermittently causes problems with them. Is there a way to export the data from the query without the dashes? If so, how?

 

[DCrake]

Yes there are ways, but what would be helpful is to post a sample of your current output highlighting the "dashes problem". You have also neglected to say how you are creating this exported data.

More info would help.

David

 

[Sonny Jim]

I am opening up a query, choosing Export, choosing Save as type: text files, checking the box for Save formatted, choosing Export All, and choosing Windows (default) as the encoding. Below is an example of the results, there is only one record in this example and I removed all but three columns to preserve the privacy of my client. The pipe delimiters line up when this is viewed in Windows Notepad.


-------------------------------------------------------------
| SHIP DATE | DELIV. DATE | STORE ID |
-------------------------------------------------------------
| 10/2/2009 | 10/1/2009 | 500 |
-------------------------------------------------------------

 

[Galaxiom]

Turn off Save Formatted. This is what puts a row of dashes between the records.

 

[Sonny Jim]

I have tried that but I then receive the message, "Too few parameters. Expected 3." This is a Parameter Query which uses 3 parameters but in opening this query in Datasheet view I have already provided the parameters.

 

[Galaxiom]

The export routines are actually implemented in VBA. So even though you have provided the parameters in the datasheet view of the open form, VBA is running it again for export. It can't get the parameters you originally supplied and doesn't ask for them. Parameters in VBA must be instantiated before the query is run.

Even if the parameters are references to controls on a form, VBA can't read them as part of a where clause but must read them directly and write their values into the clause.

You need to start you export routine by getting values of the parameters. You will need to build a query either by using an input box for your parameters or read them from a form. The latter makes more sense as you want them to match what you see when you run the original query.

I presume when you run it "export as formatted" Access uses the open query so VBA does not have to rerun the query. However as you found it adds the dashes and pipes.

 

[Sonny Jim]

Thanks a lot Galaxiom. I need to learn more about input boxes for parameters queries and query parameters read from forms. Do you have any books or Websites that you would recommend?

 

[Galaxiom]

I have learnt what I know about Access and VBA almost entirely from the Web. I Google as a I need to know. There is no better motivation than needing an answer for a real problem. Trying to read this stuff from a book is not very inspiring and retention is poor. Similarly doing exercises does not motivate like a real project. Even if it is someone elses.

I found I often ended up here, especially when I started seriously gettng into VBA. The other site I use a lot is Microsoft's developer reference. The related pages list at the bottom of most pages is a great way to expand the subject.

Often the questions I asked here would lead me into a whole new subject so I started reading other interesting looking problems. This introduced me to whole new ways of working and helped me avoid falling into traps before I came to them.

Then I started answering questions when I thought I knew the answer. I did this mainly to develop familiarity and fluency in an effort to avoid having to look up the syntax every time I needed something. The really experienced people here would add to, correct and clarify what I posted which really helped round out my knowledge. I often go and search for the answer to questions I like.

Knowledge infuses over time. Each little piece gathered may make little sense at the time but eventually the edges meet and the big picture falls into place.

The other important aspect is to try something based on what you think you understand. Do what you expect to work and then find out why it doesn't. I have learnt so much by trial and error and coming up with a wrong answer can be really informative.

 

[wiklendt]

I have learnt what I know about Access and VBA almost entirely from the Web. I Google as a I need to know. There is no better motivation than needing an answer for a real problem. Trying to read this stuff from a book is not very inspiring and retention is poor. Similarly doing exercises does not motivate like a real project. Even if it is someone elses.

I found I often ended up here, especially when I started seriously gettng into VBA. The other site I use a lot is Microsoft's developer reference. The related pages list at the bottom of most pages is a great way to expand the subject.

Often the questions I asked here would lead me into a whole new subject so I started reading other interesting looking problems. This introduced me to whole new ways of working and helped me avoid falling into traps before I came to them.

Then I started answering questions when I thought I knew the answer. I did this mainly to develop familiarity and fluency in an effort to avoid having to look up the syntax every time I needed something. The really experienced people here would add to, correct and clarify what I posted which really helped round out my knowledge. I often go and search for the answer to questions I like.

Knowledge infuses over time. Each little piece gathered may make little sense at the time but eventually the edges meet and the big picture falls into place.

The other important aspect is to try something based on what you think you understand. Do what you expect to work and then find out why it doesn't. I have learnt so much by trial and error and coming up with a wrong answer can be really informative.

hear hear!

 

[Sonny Jim]

Thanks for the advice Galxiom, sounds like a good strategy!