General discussion

Locked

Outputting Access97 query Results to a t

By lea ·
Can you help?

I am not too familiar with Visual Basic and have a problem that Microsoft product support say can only be addressed using code.

Our bank need to see records in a tabular format with variables absolutely positioned for example.A query I have created that contains the information has the records listed in columns like so.

Client Description Invoice Net Amount VAT Total Terms

The bank requires a text file that looks like this.

Client
Description
Invoice Net Amount VAT Total
Terms.

There are other variable but this is the general layout. VAT and Total have to start at position 60 and 70 respectively.

Any Ideas?

Many thanks for reading my e-mail. I hope you can help.

Kind Regards

Lea Doyle.

This conversation is currently closed to new comments.

13 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Outputting Access97 query Results to a t

by ke4vtw In reply to Outputting Access97 query ...

You can open your query in Access and choose FILE|SAVE AS/EXPORT from the menu. You'll get a dialog with two radio buttons. Choose the "To an external file or database" option. Enter the file name where you want to save the file. Change the "Save As Type" dropdown box to "Text Files" and click export. Change the radio buttons at the top of the next dialog to "Fixed Width" and then click the "Advanced" button at the bottom left of the dialog. In this dialog you can specify field widths, date formats, and set other parameters you will need to successfully format your data. You should be able to do this with NO coding at all.

Good luck!

Terry Lewis

Collapse -

Outputting Access97 query Results to a t

by lea In reply to Outputting Access97 query ...

The question was auto-closed by TechRepublic

Collapse -

Outputting Access97 query Results to a t

by mdriscol In reply to Outputting Access97 query ...

I have done several variations of this task. Records like that are called fixed format, ASCII.
If every record on the file must have exactly the same(one) format, I would use the Format() function on each field in my query to make each field exactly the right length(including empty ones!). Export the query results to a fixed format Ascii text file.
Where the output file must contain more than one record type & format, I have used an Access report to generate the file. Each record format is one line of the report. For example, the File Header comes from the Report Header; Group Header generates a different record type. Each line consists of ONE unbound text box whose value is a concatenated string made up of the fields from the report's underlying query. Again, I use the Format() function to make each field exactly the right length. Each report line is exactly the same height(.125"), font = courier new, 8 pt. One trick I find helps is to have additional report page header lines, which can be set

Collapse -

Outputting Access97 query Results to a t

by lea In reply to Outputting Access97 query ...

The question was auto-closed by TechRepublic

Collapse -

Outputting Access97 query Results to a t

by pat.schmid In reply to Outputting Access97 query ...

If I understand the problem you want to take the query results (formated on record per line) and output them on a report such that line is the Client, Line 2 is their description, line 3 is the account detail and line 4 is the terms information.

If my assumption is right, it would take quite a bit of coding, the issue is comming up to speed with all the VB objects you will need to use. VB provides the objects that make querying the data base a easy task, once the object is correctly initialized. Other objects would then allow control to format a printed report, or to create a form that displays the result a record at a time.

This is a possible workaround that may satisy your needs as well. Have you connsidered feeding your result into an Excel Spreadsheet? Odds are you would still need to create a few basic macros to get the desired results (import the results to worksheet 1 and then use macros to format the data onto worksheet 2).

Collapse -

Outputting Access97 query Results to a t

by lea In reply to Outputting Access97 query ...

The question was auto-closed by TechRepublic

Collapse -

Outputting Access97 query Results to a t

by M.R.Chambers In reply to Outputting Access97 query ...

Have you considered using the DoCmd.OutputTo command in VB? I have used this command for exporting reports,etc to text files, HTML files, ELS file, and so on.

What you do is this: if you are using some sort of user interface (ie, a pretty front end with buttons and such to allow ease of operation) set up a button and set the On_Click()event procedure to:

DoCmd.OutputTo acOutputQuery, "query name", acFormatRTF (or acFormatTXT), "destination file"

When you click the button, the codewill run and automatically output the file. acFormatRTF sends it out as Rich Text Format, which tends to handle formatting a bit better than standard TXT.

Hope that helps,

M.R.Chambers

Collapse -

Outputting Access97 query Results to a t

by lea In reply to Outputting Access97 query ...

The question was auto-closed by TechRepublic

Collapse -

Outputting Access97 query Results to a t

by shapiro In reply to Outputting Access97 query ...

Here is a sample that I believe would do the job. A little tweaking may be required.


Private Sub CreateTextFile()

Dim sFile As String
Dim sQueryName As String
Dim myDb As Database
Dim myRS As Recordset

sQueryName = "[insert your Query Name here]"
Set myDb = CurrentDb
sFile = CurrentDb.Name & "\TextOutput.txt"

Open sFile For Output As #1

Set myRS = myDb.OpenRecordset(sQueryName)

myRS.MoveFirst
Do While Not myRS.EOF
With myRS
Print #1, !Client & vbCrLf
Print #1, !Description & vbCrLf
Print #1, ![Invoice Net Amount] & " " & ![VAT Total] & vbCrLf
Print #1, !Terms & vbCrLf
Print #1, vbCrLf End With
myRS.MoveNext
Loop

Close #1

MsgBox "We are all Done with this file ..............."

End Sub


Sol.

Collapse -

Outputting Access97 query Results to a t

by lea In reply to Outputting Access97 query ...

The question was auto-closed by TechRepublic

Back to Web Development Forum
13 total posts (Page 1 of 2)   01 | 02   Next

Software Forums