[AccessD] Format a numeric column in a list box?

Jim Hewson JHewson at karta.com
Thu May 13 08:50:35 CDT 2004


Gustav,
Thanks, I'll try it.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Thursday, May 13, 2004 8:25 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Format a numeric column in a list box?


Hi Jim

> To piggyback on this thread, I have a question.
> How can you format a numeric column to align on the right?
> I want numbers to look like numbers - not text.
> This would apply to the list box and combo box.

The only way I've found to do this is to use a font where all digits
have the same width like MS Sans Serif. Further, in this font, a digit
has twice the width of a space which equals that of decimal and
thousand separators.

Having decided a maximum number you can calculate the maximum width of
the formatted number, and for any number prefix the normally formatted
number (a string) with single and double spaces to obtain a
concatenated string of the same visual width for any number.

Here is an example from a query used as rowsource for a combobox with
formatting to two decimals:

Format([Amount],Space(15-2*Len(Format([Amount],"0.00"))-Abs([Amount]>1000)) & "#,##0.00") AS SMin

and here is an example with formatting to zero decimals:

Format([Amount],Space(14-2*Len([Amount])-Abs([Amount]>1000)) & "#,##0") AS SMin

For other fonts where digits may have individual widths you would have
to expand this to count the occurrence of each digit and then
compensate with spaces.

/gustav


> Listbox rows are essentially strings.  You need to use the Format$()
> function to convert the value to a string in the query if you want a
> single decimal place in the list.

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list