Text Box columns

Posted on 2011-10-03
Last Modified: 2013-10-06
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

Question by:MikeDTE
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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.

    Author Comment

    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.

    LVL 14

    Accepted Solution

    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)
    . . .


    Author Comment


    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.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    746 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

    14 Experts available now in Live!

    Get 1:1 Help Now