Right justifying numeric datatypes while exporting to text file

Posted on 2003-03-10
Medium Priority
Last Modified: 2008-10-24
I have a table that I export to a fixed width text file, however, the numeric fields are coming across as left justified instead of right justified.  I have no leading zeros because these are numeric data types.  Can you tell me how I would export these numeric fields to make them right justified & have leading spaces for the remaining size (   112000 = 9 width size)?

As an example, my specs for the text file are as follows:

Min start at pos 68 (thru 76) with a width of 9
Max start at pos 80 (thru 88) with a width of 9
Question by:jasonck74
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
  • 3
  • 2
  • 2
  • +1
LVL 41

Accepted Solution

shanesuebsahakarn earned 2000 total points
ID: 8106373
You can retain your existing export spec, but change the field in your query to a calculated one as follows:

String$(9-Len(Str$([MyField]))," ") & Str$([MyField])

That should right-justify to 9 spaces for you.

Expert Comment

ID: 8106374
Convert it to a string:

right(space(9) & str(MyNumber), 9)


Author Comment

ID: 8106701
What if I have a field that needs to be broken up & right justified?

For example:

IOR Range is 0% - 100%

Field above broken out show just a number & no percentages should display as:

IORMin = 0
IORMax = 100

I currently have these as calculated fields, using right & mid as len formulas.

How I can I make these right justified as well?
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


Expert Comment

ID: 8106722
Just wrap what I wrote around what you're already doing:
right(space(9) & str( ... your formula ... ), 9)
LVL 41

Expert Comment

ID: 8106726
You can just wrap them with either my expression or stiemark's:

String$(9-Len(<your expression>)," ") & <your expression>

Right(Space(9) & <your expression>,9)
LVL 26

Expert Comment

by:Alan Warren
ID: 8106822

if you are preparing data to be read into an electronic banking transfer file, in Australia they are called .aba files. You cant have actual spaces you need to pad with space characters 'Space$'

Min start at pos 68 (thru 76) with a width of 9

So to create a specified width field of 26 padded with leading space characters and rightAligned bank account number use:

Space$(26-Len([BusBankAccName]))+ [BusBankAccName] AS Username,


debug.print len("                   123456")



Author Comment

ID: 8111995
It worked...thanks for your help on both questions.
LVL 41

Expert Comment

ID: 8112060
NP, glad I could help!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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

765 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