Solved

Combining String and Spaces

Posted on 2006-06-09
2
190 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

685 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