Align Text in query result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I'm using Access 2003. I have query that returns three fields from a table,
all text-based, and adds some extra formatting text. Here is my SQL:

SELECT FOOD_CATEGORY & " - " & Round(LIKE_PERCENT*100,2) & "%" & " - " &
Round(DISLIKE_PERCENT*100,2) & "%"
FROM tbl_Food_Categories;

Here is an example of the query results:

Soda - 60% - 70%
Roll - 12% - 88%
Pepperoni Pizza - 100% - 10%

My question is, how can I format/align the text so that it is like:

Soda - 60% - 70%
Roll - 12% - 88%
Pepperoni Pizza - 100% - 10%

I have tried counting characters, but if you will notice, "Soda" and "Roll"
both have 4 characters, yet they are not the same width (the lower-case L is
a very thin character compared to others), and every character seems to have
a different width, so using the Len() function to determine how man " "s to
put before the first "-" isn't helping. For example, if I did something like:

SELECT FOOD_CATEGORY & String(25-Len(FOOD_CATEGORY)," ") & " - " &
Round(LIKE_PERCENT*100,2) & "%" & " - " & Round(DISLIKE_PERCENT*100,2) & "%"
FROM tbl_MIP_Categories;

Here are the results:

Soda - 60% - 70%
Roll - 12% - 88%
Pepperoni Pizza - 100% - 10%

Any way to align the text in a query result properly?

This data will end up being dumped to 100s of Excel Spreadsheets that will
be distributed without any chance for manual editing in Excel, so I need a
programmatic solution. I am open to formatting in Excel too if that would be
easier than Access.

Any ideas?

Thanks,

Scott
 
Try this ---
SELECT Left(FOOD_CATEGORY & " ",15) &" - " &
Right(" " & Round(LIKE_PERCENT*100,2) & "%",4) & " - " & Right(" " &
Round(DISLIKE_PERCENT*100,2) & "%",4)
FROM tbl_Food_Categories;
 
Karl,

That is better than what I had (especially for the % portion), but there are
still characters that are wider than others, and the Left() function has the
same problems that the Len() function has, and the results are skewed.

But thanks for the reply, the % portion I will use.

Thanks,

-Scott
 
Karl,

Thank you for the help but you haven't quite got my issue. Adding more
spaces will not help, none of my fields are of a length near 15. Consider
this:

WWW
PPP
III

All of these are 3 characters long, yet they are clearly not the same width.
So simply adding the same number of spaces after them wouldn't solve the
problem. Here I have added 10 spaces, and a "-" after each one:

WWW -
PPP -
III -

The reason your last post was helpful on the $s is because numeric
characters all seem to be of the exact same width:

1a
2a
3a
4a
5a
6a
7a
8a
9a
0a

Notice how the "a"s all line up?

Now watch the same test scenerio for alphabetic characters:

Aa
Ba
Ca
Da
Ea
Fa
Ga
Ha
Ia
Ja
Ka
La
Ma
Na
Oa
Pa
Qa
Ra
Sa
Ta
Ua
Va
Wa
Xa
Ya
Za

If you look carefully, all of the "a"s do not line up here. Notice
I,J,N,P,W in particular.

What I'm looking for would have to be a custom property that deals with
ASCII characters and how wide each one is.

Thanks,

Scott
 
Karl,

That would work, thanks. Alas my data's final destination is a validation
drop-down list in Excel, and the font cannot be changed for Excel's data
validation lists. I would have to use a combobox (where font can be
adjusted) which I will consider doing.

Thank you for your help!

-Scott
 
Back
Top