[AccessD] Right Justify in List Box

Max Wanadoo max.wanadoo at gmail.com
Wed Feb 17 09:04:02 CST 2010


Still getting problems with this function, just wont compile. Gives error on
the int bit.
If I break it down, it will run but only give the integer part of the
currency.

I have downloaded the  Lebans code but that is so cumbersome because it uses
twips. Surely there must be another way.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 17 February 2010 11:38
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Right Justify in List Box

Hi Max

To handle negative values you will need a second section of the format
specification. Also, you may introduce Int to prevent errors for decimal
numbers:

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

The plot thickens. Did you study the solution of Lebans'?

/gustav

>>> max.wanadoo at gmail.com 17-02-2010 11:46 >>>
Gustav,

I cannot seem  to get this to work correctly.
Here is  what I have

Private Function fJustifyText(curValue As Currency) As String
    Dim str As String
    'str = Format([curValue], Space(14 - (2 * Len([curValue]))) -
Abs([curValue] > 1000)) & "#,##0"))
     str = Format([curValue], Space(20 - 2 * Len([curValue]) -
Abs([curValue] > 1000)) & "#,##0")
    fJustifyText = str
    
End Function

If I pass in a value of -165000 it puts the minus then spaces then value.

Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 16 February 2010 17:35
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Right Justify in List Box

Hi Max

If you use the font MS Sans Serif for the listbox (goes for a combobox as
well), you can use a recordsource like this:

  SELECT 
    SomeField,
    Format([AmountMin], Space(14-2*Len([AmountMin])-Abs([AmountMin]>1000)) &
"#,##0") AS SMin, 
    Format([AmountMax], Space(14-2*Len([AmountMax])-Abs([AmountMax]>1000)) &
"#,##0") AS SMax
  FROM 
    YourTable
  ORDER BY 
    SomeField;

This takes advantage of the fact that space and dot and comma for this font
consume half a "space" while digits consume a full "space".
Not very fancy but it works.

/gustav


>>> max.wanadoo at gmail.com 16-02-2010 17:12 >>>
Hi,

I am trying to get varying values in a LIST Box and have them right
justified.

This won't do the trick...

strTotal = Format(Nz(rst!AcBalance, 0), "##,##0.00")
strTotal = Space(12 - Len(strTotal)) + strTotal

Varying values give varying spacing.

Any suggestions?

Thanks

Max


-- 
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