Text Box columns

I have a need to display a variable in a text box but tabulate the elements into a series of columns

I tried this by padding out each element with spaces (see code below)
    lngSpaces = 50 - Len(Trim(jdwe_Desc))
    strEventSummary = " " & jdwe_Desc & Space(lngSpaces) & _
    Format(jdwe_StartDate, "dd-mmm-yy") & _
    Space(13 - Len(Trim(Format(jdwe_StartDate, "dd-mmm-yy")))) & _
    Space(3 - Len(Trim(jdwe_NoPlaces))) & jdwe_NoPlaces & _
    Space(9 - Len(Trim(jdwe_NoBookings))) & jdwe_NoBookings & _
    Space(9 - Len(Trim(jdwe_NoConfirms))) & jdwe_NoConfirms & _
    Space(9 - Len(Trim(jdwe_NoParticipants))) & jdwe_NoParticipants & _
    Space(9 - Len(Trim(jdwe_Reserved))) & jdwe_Reserved

The problem isn't with proportional fonts - I've tried Courier (horrible font but proportional) and the spaces between just do not move the StartDate to the same position.

What I am trying to do is line-up each column with a fixed header label like so:

Event Description                                                        Date Starts  Plcs  Bkgs  Conf  Part  Resv
Singing and Playing, Playing and Singing                        28-Jan-11    16      6       6      6        0

Then when the next event is displayed we should see:

Event Description                                                        Date Starts  Plcs  Bkgs  Conf  Part  Resv
Oboe workshop                                                            28-Jan-11    16      6       6      6        0

but what we get is:

Event Description                                                        Date Starts  Plcs  Bkgs  Conf  Part  Resv
Oboe workshop                            28-Jan-11    16      6       6      6        0

Thanks in advance ... somebody

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Is this for a form, report, or what?

Why is this data in a varaible, and not a recordset?

Why don't you just break this into its component parts and display each element in its own control?  Then you can guarantee that they line up appropriately.

Another method would be to use a listbox, but they generally align everything as text, so you would have to pad them in that as well.
MikeDTEAuthor Commented:
It's a form

It's a variable because many procedures affect the data in the string:

A first payment made on a booking increases the NoConfirms
A new booking increases the NoBookings
A cancellation decreases the NoConfirms and NoBookings

etc. etc.

So the variable is Global and is used on many of the forms to give a quick indication to the user about the status of the event.

I originally had the summary in a list box but it wasn't as easy to manipulate or display as a text field.  I could split up the single text boxes into one for each element and then position them next to each other and that is what I will do if I can't find another way.

Try using a function:

Function PadTo(vText as Variant, intCol as Integer) As String
    PadTo = Left(vText + Space(intCol), intCol)
End Function

Open in new window

then build your string field by field (allows viewing of the intermediate parts)

strEventSummary = PadTo(" " & jdwe_Desc, 50)
'Format date before passing
strEventSummary = PadTo(Format(jdwe_StartDate, "dd-mmm-yy"), 13)
strEventSummary = PadTo(jdwe_NoPlaces, 3)
. . .


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeDTEAuthor Commented:

I have no got this working with a non-propotional font but it does look a bit out of place when you compare the text with other fields on the forms.

I tried PadTo but the result is the same.  The issue is that proportional font spaces are very narrow even compared to an 'i' or 'l' which are the narrowest characters.  I cannot see any-way around using a non propotional font so I am going to split the present single text box into 7 smaller individual text boxes and stick them together and centre the text in each text box.

Thanks for all teh help - I'll divide up the points later today.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.