• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 723
  • Last Modified:

Right justifying numeric datatypes while exporting to text file

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
0
jasonck74
Asked:
jasonck74
  • 3
  • 2
  • 2
  • +1
1 Solution
 
shanesuebsahakarnCommented:
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.
0
 
stiemarkCommented:
Convert it to a string:

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


Mark
0
 
jasonck74Author Commented:
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
stiemarkCommented:
Just wrap what I wrote around what you're already doing:
right(space(9) & str( ... your formula ... ), 9)
0
 
shanesuebsahakarnCommented:
You can just wrap them with either my expression or stiemark's:

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

or:
Right(Space(9) & <your expression>,9)
0
 
Alan WarrenCommented:

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,

returns
                   123456

debug.print len("                   123456")
26

hth
Alan

0
 
jasonck74Author Commented:
It worked...thanks for your help on both questions.
0
 
shanesuebsahakarnCommented:
NP, glad I could help!
0

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!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now