Web Development·12,146 discussions

Outputting Access97 query Results to a t

Locked

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.

Topic

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

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

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

The question was auto-closed by TechRepublic

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

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

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

The question was auto-closed by TechRepublic

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

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).

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

The question was auto-closed by TechRepublic

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

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

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

The question was auto-closed by TechRepublic

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

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.

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

The question was auto-closed by TechRepublic

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

I would suggest that you first create a report with each line formatted as needed in the text file. (I assume you want four lines for each record.) The form would have no headers or footers, just detail.

Use a fixed pitch font (Courier is good)for each field. The third line, which combines five fields, should be defined by using format and related functions to place the fields in a string that is initially 80 spaces (or whatever line length you want). If you format each field and just try to concatenate, some of the spaces might disappear, so experiment.

Once you have the report working correctly (including generating it from a query that includes the selection criteria), using the DoCmd.OutputTo function as mentioned above should work fine. (OutputTo does not permit you to include where parameters as part of the DoCmd statement, just the report name.)

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

The question was auto-closed by TechRepublic

Outputting Access97 query Results to a t

In reply to Outputting Access97 query Results to a t

This question was auto closed due to inactivity

[Author]

Replies