Tab formating in a String Value

[BamaColtsFan]

Okay, I am using a simple loop and pulling together a list of data into a string value while trying to make it look like a table of information. Everything works pretty well except that I'm having trouble getting my columns to line up. I have used vbTab to seperate the columns and that works most of the time. The problem is that occasionally, the data can be significantly longer or shorter than average. For example, the name Doe, John is a lot shorter than Smith, Christopher and causes my tabs to get out of alignment. With that said, my question is this: How can I format my tabs (or my data) so that my columns remain consistient at all times?

Set myBatchList = db.OpenRecordset("qryBatchMailBody")

BatchList = "Name" & vbTab & vbTab & vbTab & vbTab & "ST" & vbTab & "Dept" & vbTab & "People Group" & vbTab & vbTab & _
            "Status" & vbTab & "End Date" & vbCrLf

Do Until myBatchList.EOF

BatchList = BatchList & vbCrLf & myBatchList("Name") & vbTab & vbTab & myBatchList("SkillType") & vbTab & _
            myBatchList("Dept") & vbTab & myBatchList("People Group") & vbTab & myBatchList("Status") & _
             vbTab & Format(myBatchList("Timecard Stop Date"), "dd-mmm-yyyy")

myBatchList.MoveNext

Loop

As always, any advice is deeply appreciated!

 

[KenHigg]

I think you're either going to have to test for the max length or force them to a certain length which would cut off the end of long text.

 

[BamaColtsFan]

I think you're either going to have to test for the max length or force them to a certain length which would cut off the end of long text.

At the risk of revealing my ignorance, how would I "force them to a certain length"? I think I would prefer that over letting the string get too long....

 

[KenHigg]

Build a query to use as the source instead of the original table and do something like the following for each field:

MyNewFieldName: Left(MyOldFieldName,10)

 

[BamaColtsFan]

Build a query to use as the source instead of the original table and do something like the following for each field:

MyNewFieldName: Left(MyOldFieldName,10)

Oh, right... Forgot about that trick! But what if the value of MyOldFieldName is short? This would deal with long data but the same (if opposite) problem exists for short values....

 

[KenHigg]

To start with I think I would ditch the tabs thing and do something like:

MyNewFieldName: Left(MyOldFieldName & " ",10)

I didn't test this, let me know if it works

 

[BamaColtsFan]

Nope... didn't work... Didn't actually do anything in terms of lining up the columns...

 

[gemma-the-husky]

how are you trying to display - in a msgbox?

the problem is that the msgbox font is proportional spaced - and to make columns to line up you need a fixed space font (try courier)

i tend to use msgboxes, and ignore the problem - unless its important - in which case i do go to the trouble of counting the characters etc - but its a lot more messing about.

 

[BamaColtsFan]

how are you trying to display - in a msgbox?

the problem is that the msgbox font is proportional spaced - and to make columns to line up you need a fixed space font (try courier)

i tend to use msgboxes, and ignore the problem - unless its important - in which case i do go to the trouble of counting the characters etc - but its a lot more messing about.

Hey Dave!

Actually, I'm using the value as a token replacement in the body of an e-mail message. It gets inserted into a saved text file and then that is used as the body of the e-mail. Everything works great except that the formatting can get a little ugly if the length of data is long or short...

 

[gemma-the-husky]

maybe its the same thing - what font is the pasted data. is it a proportional spaced font, or a fixed space font.

 

[BamaColtsFan]

The text file is Courier New 10 point... same when the e-mail goes out. I think the problem is a lack of spaces on the short data (and too many on the long) so I don't think proportional spacing is really an issue. I'm thinking maybe I can build a function that either trims or pads the length of the string but I'm not sure how to "pad" empty spaces on a text string....

 

[KenHigg]

I think my post got zonked when the html kicked in. The drill was to add ten spaces to the right of the text then trim it down from the left to 10. It should work.

 

[BamaColtsFan]

Well, I did a bit of research and testing on left and right functions and I'm now pretty confused...

To start with I used (for example)

Right(Space(10) & myBatchList("Status"), 10)

That gave me the value of Status, exactally 10 characters, long but aligned to the right.

Next, I reversed it and tried

Left(Space(10) & myBatchList("Status"), 10)

But that way, I got nothing for the value of Status.

So why does Right work but Left doesn't? I'm obviously goofing it up somehow...

 

[KenHigg]

It depends on which side you put the spaces. You need to trim off the other side.

Left(myBatchList("Status") & Space(10), 10)

 

[BamaColtsFan]

Okay! That did it! Works for both short and long values! Thanks Ken!

 

[KenHigg]

Cool - Glad you have it working.