Exporting a table/Query to a text and Price columns won't align ri

[Marc]

I am exporting a fixed width table to a text file. Everything exports to the
text file just fine except for my two price columns. My problem is when I
export, the Price columns are not right aligned. I tried getting the two
columns to be right aligned in a query using Format(Unit_Price,"0000000.00")
AS Expr2. When I view the query the two fields look correct but when I export
it they end up being left aligned. I can't have them be justified to the
left. I've tried many things and I can't get it to work. Does anyone have
any ideas on how I can export my table or query and have my two price columns
be right justified?

 

[Jack Cannon]

Try converting the price fields to text.
Prepend a series of spaces (" 12.34")
Then use the Right command to extract the proper width.
Right(" 12.34", 6) yields " 12.34"
I did not test this but it might work in your application

There are other possibilities:

If the data is going into a Word Processing document then
you should be able to add a TAB character before the
price stream and set the equivalent TAB position to
Right Tab.

If the data is going into a spreadsheet then the column
can the Right Adjusted.

Jack Cannon

 

[Jerry Whittle]

You couldtry the String function to pad it with leading zeros.

Padded: String(10-Len([Unit_Price]),"0") & [Unit_Price]

However Unit_Price will need to be a string and not a number or currency
field. You'll also have to make sure that Unit_Price is not null as the
String function doesn't play well with null values.

 

[Marc]

I tried your statement below in the query. It still didn't work. Now do I
need to go to the table and change the number to a string because I didn't
see any options to change the field to a string. Sorry I don't understand.

You couldtry the String function to pad it with leading zeros.

Padded: String(10-Len([Unit_Price]),"0") & [Unit_Price]

However Unit_Price will need to be a string and not a number or currency
field. You'll also have to make sure that Unit_Price is not null as the
String function doesn't play well with null values.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am exporting a fixed width table to a text file. Everything exports to the
text file just fine except for my two price columns. My problem is when I
export, the Price columns are not right aligned. I tried getting the two
columns to be right aligned in a query using Format(Unit_Price,"0000000.00")
AS Expr2. When I view the query the two fields look correct but when I export
it they end up being left aligned. I can't have them be justified to the
left. I've tried many things and I can't get it to work. Does anyone have
any ideas on how I can export my table or query and have my two price columns
be right justified?

 

[Jack Cannon]

Marc,

Jerry and I are both saying the same thing - just a slightly different
approach.
In both cases the object is to convert the Price field to a string and then
pad the string with the appropriate number of characters.

You must do this in the query - not the table.

Here is the field in the query using my approach.
strUnit_Price: Right(Space(10) & CStr([Unit_Price]),10)

I am padding with spaces if you want to pad with some other character (0 _
etc.) then you need to use Jerry's formula in a similar manner as I
demonstrated.

Jack Cannon

I tried your statement below in the query. It still didn't work. Now do I
need to go to the table and change the number to a string because I didn't
see any options to change the field to a string. Sorry I don't understand.

You couldtry the String function to pad it with leading zeros.

Padded: String(10-Len([Unit_Price]),"0") & [Unit_Price]

However Unit_Price will need to be a string and not a number or currency
field. You'll also have to make sure that Unit_Price is not null as the
String function doesn't play well with null values.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am exporting a fixed width table to a text file. Everything exports to the
text file just fine except for my two price columns. My problem is when I
export, the Price columns are not right aligned. I tried getting the two
columns to be right aligned in a query using Format(Unit_Price,"0000000.00")
AS Expr2. When I view the query the two fields look correct but when I export
it they end up being left aligned. I can't have them be justified to the
left. I've tried many things and I can't get it to work. Does anyone have
any ideas on how I can export my table or query and have my two price columns
be right justified?

 

[Marc]

Jack,
I've tried putting it in the query and in SQL. It still won't align
right in the text file. Not sure what to try next.

Marc,

Jerry and I are both saying the same thing - just a slightly different
approach.
In both cases the object is to convert the Price field to a string and then
pad the string with the appropriate number of characters.

You must do this in the query - not the table.

Here is the field in the query using my approach.
strUnit_Price: Right(Space(10) & CStr([Unit_Price]),10)

I am padding with spaces if you want to pad with some other character (0 _
etc.) then you need to use Jerry's formula in a similar manner as I
demonstrated.

Jack Cannon

I tried your statement below in the query. It still didn't work. Now do I
need to go to the table and change the number to a string because I didn't
see any options to change the field to a string. Sorry I don't understand.

You couldtry the String function to pad it with leading zeros.

Padded: String(10-Len([Unit_Price]),"0") & [Unit_Price]

However Unit_Price will need to be a string and not a number or currency
field. You'll also have to make sure that Unit_Price is not null as the
String function doesn't play well with null values.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am exporting a fixed width table to a text file. Everything exports to the
text file just fine except for my two price columns. My problem is when I
export, the Price columns are not right aligned. I tried getting the two
columns to be right aligned in a query using Format(Unit_Price,"0000000.00")
AS Expr2. When I view the query the two fields look correct but when I export
it they end up being left aligned. I can't have them be justified to the
left. I've tried many things and I can't get it to work. Does anyone have
any ideas on how I can export my table or query and have my two price columns
be right justified?

 

[Jack Cannon]

Marc,

You must keep in mind that a text file does not have the ability to make a
right alignment. The suggestions that Jerry and I offered will only make it
appear to align right. If it still does not appear to align right then it
could be the font that you are using to view the text file. Many fonts are
proportionally spaced. This means that a space character is allowed very
little area and sometimes does not appear to even be in the file. Try using
a monospaced font such as Courier.

To truly accomplish a right alignment the file would have to be viewed in a
spreadsheet or a word processor. In each case you will have to apply the
proper format - such as a number cell or a right tab.

Jack Cannon

Jack,
I've tried putting it in the query and in SQL. It still won't align
right in the text file. Not sure what to try next.

Marc,

Jerry and I are both saying the same thing - just a slightly different
approach.
In both cases the object is to convert the Price field to a string and then
pad the string with the appropriate number of characters.

You must do this in the query - not the table.

Here is the field in the query using my approach.
strUnit_Price: Right(Space(10) & CStr([Unit_Price]),10)

I am padding with spaces if you want to pad with some other character (0 _
etc.) then you need to use Jerry's formula in a similar manner as I
demonstrated.

Jack Cannon

I tried your statement below in the query. It still didn't work. Now do I
need to go to the table and change the number to a string because I didn't
see any options to change the field to a string. Sorry I don't understand.

:

You couldtry the String function to pad it with leading zeros.

Padded: String(10-Len([Unit_Price]),"0") & [Unit_Price]

However Unit_Price will need to be a string and not a number or currency
field. You'll also have to make sure that Unit_Price is not null as the
String function doesn't play well with null values.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am exporting a fixed width table to a text file. Everything exports to the
text file just fine except for my two price columns. My problem is when I
export, the Price columns are not right aligned. I tried getting the two
columns to be right aligned in a query using Format(Unit_Price,"0000000.00")
AS Expr2. When I view the query the two fields look correct but when I export
it they end up being left aligned. I can't have them be justified to the
left. I've tried many things and I can't get it to work. Does anyone have
any ideas on how I can export my table or query and have my two price columns
be right justified?