Right Aligning Text for Fields in ListBox Control (Access 2003)

[edmscan]

I would like to right align my text for some fields in my list box control. Is there a better list box control out there than the standard one ? Or .. any advice on how to align the text. The default left alignment doesn't work for all my fields.
I tried to search but came up empty.

I did try a function "Justify String" I found on the internet, but it didn't work for me. Don't know if I was just using it wrong.

This is what I tried .. Call JustifyString("frmExercise","List16",ExerciseTime, 2,True,False)

Here is info from the actual function.

Function JustifyString(myform As String, myctl As String, myfield As Variant, _
col As Integer, RightOrCenter As Integer, Optional Sform As String = "") As Variant

Thanks
Kevin

[June7]

Why didn't it work, what happened - error message, wrong results, nothing?

Can you post the link?

Post the rest of the function code.

[edmscan]

Here is the function .... I did not write it, as I am not that good at Access. But when I tried it .. nothing changes.
I would be interested if I am doing it wrong .. as far as calling the function.

My Form .. frmExercise
My listbox control .. lstExercise (I renemed it)
Listbox fields I need to right justify .. ExerciseTimeOnly (2nd field from left), ExerciseTime (4th field from left), ExerciseCaloriesBurned (5th field from left)

I used in my code .. JustifyString("frmExercise", "lstExercise", ExerciseTime, 3, True, False)

No errors or anything .. just no change.

'*************************************************
' 1) We now call the function with an Optional SubForm parameter. This is
' the name of the SubForm Control. If you used the Wizard to add the
' SubForm to the main Form then the SubForm control has the same name as
' the SubForm. But this is not always the case. For the benefit of those
' lurkers out there<bg> we must remember that the SubForm and the SubForm
' Control are two seperate entities. It's very straightforward, the
' SubForm Control houses the actual SubForm. Sometimes the have the same
' name, very confusing, or you can name the Control anything you want! In
' this case for clarity I changed the name of the SubForm Control to
' SFFrmJustify. Ugh..OK that's not too clear but it's late!
'
' So the adjusted SQL statement is now.
' CODENUM: JustifyString("FrmMain","List5",[code],0,True,"SFfrmJustify")
'*************************************************



' ***CODE START
Function JustifyString(myform As String, myctl As String, myfield As Variant, _
col As Integer, RightOrCenter As Integer, Optional Sform As String = "") As Variant

' March 21, 2000
' Changes RightOrCenter to Integer from Boolean
' -1 = Right. 0 = Center, 1 = Left

' Called from UserDefined Function in Query like:
' SELECT DISTINCTROW JustifyString("frmJustify","list4",_
' [code],0,False) AS CODENUM, HORTACRAFT.NAME FROM HORTACRAFT;

' myform = name of form containing control
' myctl = name of control
' myfield is the actual data field from query we will Justify
' col = column of the control the data is to appear in(0 based index)
' RightOrCenter True = Right. False = Center

Dim UserControl As Control
Dim UserForm As Form
Dim lngWidth As Long

Dim intSize As Integer
Dim strText As String
Dim lngL As Long
Dim strColumnWidths As String
Dim lngColumnWidth As Long
Dim lngScrollBarWidth As Long
Dim lngOneSpace As Long
Dim lngFudge As Long
Dim arrCols() As String
Dim lngRet As Long

' Add your own Error Handling
On Error Resume Next

' Need fudge factor.
' Access allows for a margin in drawing its Controls.
lngFudge = 60

' We need the Control as an Object
' Check and see if use passed SubForm or not
If Len(Sform & vbNullString) > 0 Then
Set UserForm = Forms(myform).Controls(Sform).Form
Else
Set UserForm = Forms(myform)
End If

' Assign ListBox or Combo to our Control var
Set UserControl = UserForm.Controls.Item(myctl)

With UserControl
If col > Split(arrCols(), .ColumnWidths, ";") Then Exit Function
If col = .ColumnCount - 1 Then
' Add in the width of the scrollbar, which we get in pixels.
' Convert it to twips for use in Access.
lngScrollBarWidth = GetSystemMetrics(SM_CXVSCROLL)
lngScrollBarWidth = lngScrollBarWidth * (1440 / GetTwipsPerPixel())
End If
lngColumnWidth = Nz(Val(arrCols(col)), 1)
lngColumnWidth = lngColumnWidth - (lngScrollBarWidth + lngFudge)
End With

' Single space character will be used
' to calculate the number of SPACE characters
' we have to add to the Input String to
' achieve Right justification.
strText = " "

' Call Function to determine how many
' Twips in width our String is
lngWidth = StringToTwips(UserControl, strText)

' Check for error
If lngWidth > 0 Then
lngOneSpace = Nz(lngWidth, 0)

' Clear variables for next call
lngWidth = 0

' Convert all variables to type string
Select Case VarType(myfield)

Case 1 To 6, 7
' It's a number(1-6) or 7=date
strText = Str$(myfield)

Case 8
' It's a string..leave alone
strText = myfield

Case Else
' Houston, we have a problem
Call MsgBox("Field type must be Numeric, Date or String", vbOKOnly)

End Select

'let's trim the string - better safe than sorry :-)
strText = Trim$(strText)

' Call Function to determine how many
' Twips in width our String is
lngWidth = StringToTwips(UserControl, strText)

' Check for error
If lngWidth > 0 Then

' Calculate how many SPACE characters to append
' to our String.
' Are we asking for Right or Center Alignment?
Select Case RightOrCenter
Case -1
' Right
strText = String(Int((lngColumnWidth - lngWidth) / lngOneSpace), " ") & strText

Case 0
' Center
strText = String((Int((lngColumnWidth - lngWidth) / lngOneSpace) / 2), " ") & strText _
& String((Int((lngColumnWidth - lngWidth) / lngOneSpace) / 2), " ")

Case 1
' Left
strText = strText

Case Else
End Select
' Return Original String with embedded Space characters
JustifyString = strText
End If
End If

' Cleanup
Set UserControl = Nothing
Set UserForm = Nothing

End Function

[edmscan]

I have noticed that the UserForm and UserControl objects are set to Nothing in the debugger. Not quite right to me.

[June7]

That function is to create a fixed length string by 'padding' with a repeated character. I think it determines the output length dynamically from the listbox properties. I do 'padding' with a very simple function. My code has an argument for the total string length. So if you want to specify the output length, here is the procedure.

Function Pad(varText As Variant, strAlign As String, intLength As Integer, Optional strFill As String = " ") As String
If Len(varText) >= intLength Then
    'if the source string is longer than the specified length, return the Length left characters
    Pad = Left(varText, intLength)
ElseIf strAlign = "L" Then
    'text aligns left, fill out the right with spaces
    Pad = varText & String(intLength - Len(varText), strFill)
Else
    'text aligns right, fill out the left with spaces
    Pad = String(intLength - Len(varText), strFill) & varText
End If
End Function

Example of calling the function:

Pad([fieldname], "R", 15, " ")

However, this code will error if the input for varText is Null. So deal with possible null:

Pad(Nz([fieldname],""), "R", 15, " ")

I will try to debug your procedure later.

[edmscan]

Here is what I have (RowSource of my ListBox) ... but for some reason the last field "ExerciseCaloriesBurned" will not right justify. Not really sure why as I don't see an issue.

SELECT DISTINCTROW qryExerciseListBox.ExerciseDate, JustifyString("frmExercise","lstExercise",[ExerciseTimeOnly],1,True) AS Expr3, qryExerciseListBox.ExerciseName, JustifyString("frmExercise","lstExercise",[ExerciseTime],3,True) AS Expr1, JustifyString("frmExercise","lstExercise",[ExerciseCaloriesBurned],4,True) AS Expr8
FROM qryExerciseListBox
WHERE (((qryExerciseListBox.ExerciseDate)>Date()-Weekday(Date(),2)+0 And (qryExerciseListBox.ExerciseDate)<Date()-Weekday(Date(),2)+8))
ORDER BY qryExerciseListBox.ExerciseDate, JustifyString("frmExercise","lstExercise",[ExerciseTimeOnly],1,True);

[June7]

Since it works on all but 1 field, fruitless for me to test with my data. If you want to provide db for analysis, follow instructions at bottom of my post.

An alternative is the function I provided or using the intrinsic String function directly.

String(15 - Len([ExerciseCaloriesBurned]), " ") & [ExerciseCaloriesBurned] AS Expr8

15 was a random choice, use whatever length you want.

I just realized Len function will error on number data. Is ExerciseCaloriesBurned a number field? Your function is supposed to handle that.

[edmscan]

Here is my solution ... ExerciseCaloriesBurned is usually 2 or 3 characters. It is numeric, and this works for 99% of the data there will be. The Len function seems to work fine on the numeric data. I tried using cSTR but it was unneeded.

SELECT DISTINCTROW qryExerciseListBox.ExerciseDate, JustifyString("frmExercise","lstExercise",[ExerciseTimeOnly],1,True) AS Expr1, qryExerciseListBox.ExerciseName, JustifyString("frmExercise","lstExercise",[ExerciseTime],3,True) AS Expr2, IIf(Len([ExerciseCaloriesBurned])=3,String(9-Len([ExerciseCaloriesBurned])," ") & [ExerciseCaloriesBurned],String(10-Len([ExerciseCaloriesBurned])," ") & [ExerciseCaloriesBurned]) AS Expr3
FROM qryExerciseListBox
WHERE (((qryExerciseListBox.ExerciseDate)>Date()-Weekday(Date(),2)+0 And (qryExerciseListBox.ExerciseDate)<Date()-Weekday(Date(),2)+8))
ORDER BY qryExerciseListBox.ExerciseDate, JustifyString("frmExercise","lstExercise",[ExerciseTimeOnly],1,True);

[June7]

My testing of Len function on number value failed. Oh well, glad this works for you.

However, don't understand why you are using the IIf.

String(12-Len([ExerciseCaloriesBurned])," ") & [ExerciseCaloriesBurned]

will result in a 12 character string regardless of the length of [ExerciseCaloriesBurned], unless it's null, in which case it doesn't really matter, or is longer than 12, which will error.

[edmscan]

I tried what you gave me first.. and well the numbers did not line up right. The numbers with 2 digits lined up differently then the ones with 3 digits. They were not correctly right aligned. Cannot tell you more than that. Probably something to do with how it calculated the length of the numbers.

[June7]

I forgot to mention that this is best done with non-proportional font. I like Consolas.

Then shouldn't need the IIf.