Solved

Combining String and Spaces

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month4 days, 13 hours left to enroll

636 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