formatting variable length strings

[gemma-the-husky]

is there a way to use format or some other command to force columns to line up in a text box - ie to oocupy a specific number of characters.

do you need a fixed size font - is there one?


so

ID__________Text_________Value
1___________MyText_________12
23__________MyNextText____213

ie format (id,4 char length)
format (text, 20 char length say)

[edited
now found courier font, which is OK, however, with

mydetail = mydetail & vbCrLf & Format(rst!headertext, "@@@") & " " & _
Format(rst!LoadText, "@@@@@@@@@@@@@@@") & " " & Format(rst!items, "######0")

headertext is always 1 char, so thats OK
loadtext is being right justified, (but i want it left justified)
items is showing as 19, and 9, but not aligning - (they are actually left-aligning, instead of right aligning)
how should I set out the format masks to get this working please

 

[pbaldy]

I'm not sure about format, but you could use the Space function along with the Len function and concatenate that with the field to pad with spaces. Let me know if you need a sample.

 

[ajetrumpet]

is there a way to use format or some other command to force columns to line up in a text box - ie to oocupy a specific number of characters.

Columns in a text box? Can you elaborate a bit? Not sure what you mean.

And - occupying a number of characters? Do you mean padding?

I was going to say the same thing you did Paul. Sounds like the thing to do...

 

[gemma-the-husky]

space was great

the only problem now is the numbers

i am doing format(mynumber, "#####0")

but it is left justifying the numbers, instead of rightjustifying so i am getting

19____
1_____
9_____ etc

instead of

____19
_____1
_____9 etc

(ignore the underscores - its just to demonstrate alignment)


adam what i'm trying to do is fill a text box with a string made of 3 variables, but so the variables line up in consecutive lines.

 

[ajetrumpet]

are all of the variables of different data type? From your first post, I would assume not. You may have to conditionalize the coding to death to make this work.

I'd like to see the entire block that you are working with here. It would paint a better picture...

 

[gemma-the-husky]

i'm iterating a totalsquery that has two text group by columns, and a count column (items) - so that is definitely numeric

Set rst = CurrentDb.OpenRecordset("myquery")

normaldetail = "Header      Desc                    Qty"
While Not rst.eof
    normaldetail = normaldetail & vbCrLf "  " & rst!headertext & "       " & _
            rst!LoadText & Space(25 - Len(rst!LoadText)) & "    " & _
            [COLOR="Red"]Format(rst!items, "######0")[/COLOR]
    rst.MoveNext
Wend

thats all it is - the line in red is filling the format string from the left, not the right, although its definitely numeric.

 

[gemma-the-husky]

Sub tryit()
MsgBox (Format(123, "#############0"))
End Sub

in fact, if you do the above, it left justifies the number in the format string

what im trying to do is this

Sub tryit()
MsgBox (Format(123, "0") & vbCrLf & _
Format(12, "0"))
End Sub

so that the numbers line up like numbers ought to do

 

[gemma-the-husky]

ridiculous - i couldnt get the mask to work, so ive got round it like this
doesnt quite work in a msgbox though, as its using a proportional font
shouldnt have to use spaces though to align numbers surely

Sub tryit()
MsgBox (Space(5 - Len(CStr(123))) & 123 & vbCrLf & _
Space(5 - Len(CStr(12))) & 12)
End Sub

 

[ajetrumpet]

So is this fixed then?

I'm sure I could offer you some other help if I tinkered with this a bit...

Is it possible that you are trying to make the program bend in ways it cannot bend?


I bet you could bring in some complexity here using LEN(), INSTR(), and RIGHT() to get exactly what you want, but it might be cumbersome!!

 

[gemma-the-husky]

i've got a fix, so its ok - its just that i couldn't use the mask in the way i thought it would work

what about this then

Sub trytext()
MsgBox (Format("short", "@@@@@@@@@@@@@@@@") & vbCrLf & _
Format("not so short", "@@@@@@@@@@@@@@@@"))
End Sub

i would have expected strings to be left masked, but they are not - they seem to be RIGHT masked.

[EDIT finally found this one in help - you need a bang mark to force left justify do

Sub trytext()
MsgBox (Format("short", "!@@@@@@@@@@@@@@@@") & vbCrLf & _
Format("not so short", "!@@@@@@@@@@@@@@@@"))
End Sub

works OK

 

[ajetrumpet]

what about this then

Sub trytext()
MsgBox (Format("short", "@@@@@@@@@@@@@@@@") & vbCrLf & _
Format("not so short", "@@@@@@@@@@@@@@@@"))
End Sub

What about what? I tried that, and the padding with those symbols occurs on the left side. Isn't that what you wanted? You keep referring to "padding" as "masking"...

 

[gemma-the-husky]

Sub trytext()
MsgBox (Format("short", "@@@@@@@@@@@@@@@@") & vbCrLf & _
Format("not so short", "@@@@@@@@@@@@@@@@"))
End Sub

what i've found is that with this, the characters in "short" are placed to the rightmost placeholders, so you get

@@@@@@@@@short (with @ becoming blanks) or
@@@@notsoshort

and the strings dont left align, as I expected them to. As I then found, putting a bang at the front of the mask string fixes it.

----------------
with numbers there doesnt seem to be any way to force alignment

so format(123,"0000")
and format(12,"0000")

present as
0123
0012

but if you replace the 0's with #symbols

then format(123,"###0")
and format(12,"###0")

present as

123
12


and there is no way you can force the blank numbers to display as blanks, and therefore units columns to align.


Enough -

Bed Time

 

[ajetrumpet]

what i've found is that with this, the characters in "short" are placed to the rightmost placeholders, so you get

@@@@@@@@@short (with @ becoming blanks) or
@@@@notsoshort

We are on the same page here (finally).

As I then found, putting a bang at the front of the mask string fixes it.

That doesn't surprise me. That's the same behavior as you find in SQL statements.

but if you replace the 0's with #symbols

then format(123,"###0")
and format(12,"###0")

present as

123
12


and there is no way you can force the blank numbers to display as blanks, and therefore units columns to align.

Well, that has to do with the data type issue I believe (and the nature of the formatting characters you are using). After all, the pound sing is an optional character.

I was wondering too, if the leading zeros work for your purposes, why you couldn't use that method, and just color the zeros (by way of the Left() function?) to match the backcolor of the textbox??


Enough -

Bed Time for me too.