Exporting a table to a fixed width in Access 2002

Posted on 2005-03-01
Medium Priority
Last Modified: 2008-03-06
I have a command button on a form that runs a bunch of queries with the end result producing a table called final_tbl.
The table then gets exported as a text file to be uploaded to our mainframe. The exporting of the table works fine but I was thrown the following curve. The final_tbl contains fields like ticket, quantity, location, and so on. When uploading the text file into our mainframe it must match or line up against and 80 column record length or line position. So for example the first field which is ticket must be uploaded and start in position or column 2 When I look at the text file it starts ticket in position 1. This is a problem for the rest of my fields as well.  I thought I could go into the export specification wizard and change start/width from 1 to 2 along with the rest of my fields but I get an error message that says "there is a gap between fields and ticket. So my question is how do I set a fixed range for my fields that have gaps:

Example: ticket (field length is 5) (needs to be in position 2),  quantiy (position 9) leaving a gap between position 8 and so on. I hope I made sense.
Question by:partykdj1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 18

Expert Comment

ID: 13430735
why as a text file and not csv?
Surely that's limiting in terms of working with the data?

What type of db is it going into?
LVL 41

Expert Comment

ID: 13430848
So are you saying that the first character on each line needs to be a space? If so, just write a query that adds a space in front of ticket, and set that field to start from position 1 instead of 2.

Author Comment

ID: 13431029
how do I write that query to show spaces
LVL 41

Accepted Solution

shanesuebsahakarn earned 300 total points
ID: 13431058
Just something like this:

Expr1: " " & [ticket]

instead of the ticket field.

Author Comment

ID: 13431076
I'll try it...

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question