Solved

Combining String and Spaces

Posted on 2006-06-09
2
185 Views
Last Modified: 2010-04-07
Hello,

I am reading a bunch of records from a SQL DB, populating the records found into an array, and then populating a list box with 2 fields of data from the array records.

I want the 3 values to show in a straight column separated by a specific number of spaces regardless of how long the value is.

For example:

SQL DB has 8 Columns
Id Name Col3 Col4 Col5 Col6 Col7 Col8

Array
Dim myArray(1 to 999, 1 to 8)

I set the array equal to all the values i find in SQL DB
Ctr = 1
sqlData.MoveFirst

Do Until sqlData.EOF Or Ctr > 999
    ReportList(Ctr, 1) = sqlData!ID
    ReportList(Ctr, 2) = sqlData!Name
    ReportList(Ctr, 3) = sqlData!Col3
    ReportList(Ctr, 4) = sqlData!Col4
    ReportList(Ctr, 5) = sqlData!Col5
    ReportList(Ctr, 6) = sqlData!Col6
    ReportList(Ctr, 7) = sqlData!Col7
    ReportList(Ctr, 8) = sqlData!Col8
    Ctr = Ctr + 1
    sqlData.MoveNext
Loop

Then I populate a list box with 2 of the fields for the user to see and select
Ctr = 1
Do Until Ctr > 999 Or ReportList(Ctr, 1) = ""
RptListBox.AddItem Left(ReportList(Ctr, 2) & "                              ", 30) & ReportList(Ctr, 3)  <-------------here is the problem
Ctr = Ctr + 1
Loop

I want the List box to look like this
Record 1 data which is this big               Record 1 data second field
Record 2 data only this big                     Record 2 data second field
Recrod 3 data small                              Record 3 data second field

Instead it looks like this
Record 1 data which is this big               Record 1 data second field
Record 2 data only this big                Record 2 data second field
Record 3 data small                Record 3 data second field

So how can i sort of "Space fill" the string so that the columns line up?

MANY MANY THanks!!!

Eric
0
Comment
Question by:posae
2 Comments
 
LVL 9

Accepted Solution

by:
dancebert earned 125 total points
ID: 16872542
You need two things:

A monospace font:  http://www.lowing.org/fonts/

A pad function:

Option Explicit
' ******************************************************************
'   Pads a string with spaces (by default) or any other character
' to a specified length.
' Parameters:
'       p_lDirection - Left or Right
'       p_sSource - String to be padded
'       p_lNewWidth - length of padded string
'       p_sPadCharacter - character to pad with.
' Returns: the padded string

' Example: MyStr = Pad("R", "Hello", 10)
' Returns: "Hello     " (no quotes)
' Example: MyStr = Pad("L", "Hello", 10, ".")
' Returns: ".....Hello" (no quotes)

' By: Craig H. Rettig
' Date: November 2000

' Get more free code samples at http://www.BitbucketHeaven.com/
' ******************************************************************

Public Enum eDirection
    eLeft = 1
    eRight
End Enum

Function Pad(ByVal p_lDirection As eDirection, _
                ByVal p_sSource As String, _
                ByVal p_lNewWidth As Integer, _
                Optional ByVal p_sPadCharacter As String = " ") As String

' If string length is greater than or equal to the requested amount, don't change it.
    If Len(p_sSource) >= p_lNewWidth Then
        Pad = p_sSource
    Else
        If p_lDirection = eDirection.eLeft Then
            Pad = Right$(String(p_lNewWidth, p_sPadCharacter) & p_sSource, p_lNewWidth)
        Else
            Pad = Left$(p_sSource & String(p_lNewWidth, p_sPadCharacter), p_lNewWidth)
        End If
    End If

End Function
0
 
LVL 5

Expert Comment

by:lunchbyte
ID: 16872962
I assume the second column is hidden (beyond the width of the combo box) and you use that to store certain information.

If that is the case then here is what I do.

RptListBox.AddItem value & space(100) & value_id

When I need the value id I would do this.
value_id = trim(right(RptListBox.AddItem, 100))

I know that right 100 will pick up all the value id information and the trim will remove all the leading spaces.




0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now