Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combining String and Spaces

Posted on 2006-06-09
2
Medium Priority
?
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Accepted Solution

by:
dancebert earned 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

719 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