Solved

Combining String and Spaces

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

708 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

12 Experts available now in Live!

Get 1:1 Help Now